Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies. Only from Platform Scholar.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
=14-LEN(SUBSTITUTE(SUBSTITUTE(CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1,M1,N1),"000","###"),"#",""))
The above assumes that, for example, four zeros in a row only count as three (because the the three zeros have been "used up" before the fourth one comes along). So for your example, it gives six, but it also gives six for the following...{=SUM(IF((A1:L1&""="0")*(B1:M1&""="0")*(C1:N1&""="0"),3-IF(D1:O1&""="0",2,0)))}
Regards,Option Explicit
Sub Three_in_a_Row()
Dim xLast_Row As Long
Dim i As Long
Dim j As Long
Dim xZeros As Long
Dim xCount As Long
Sheets("Sheet1").Activate
xLast_Row = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row
For i = 1 To xLast_Row
xCount = 0
xZeros = 0
For j = 1 To 14
If Cells(i, j) = "0" Then
xZeros = xZeros + 1
If xZeros = 3 Then
xCount = xCount + 3
ElseIf xZeros > 3 Then
xCount = xCount + 1
End If
Else
xZeros = 0
End If
Next
Cells(i, 17) = xCount
Next
End Sub
{=SUMPRODUCT((FREQUENCY(IF(A1:N1&""="0", COLUMN(A1:N1)), IF(A1:N1&""<>"0", COLUMN(A1:N1)))>=3)*(FREQUENCY(IF(A1:N1&""="0", COLUMN(A1:N1)), IF(A1:N1&""<>"0", COLUMN(A1:N1)))))}
Regards,If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.