=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,Title | # Comments | Views | Activity |
---|---|---|---|
Pivot help | 3 | 21 | |
Input box prompt to specify column letter using VBA | 4 | 8 | |
Excel | 6 | 13 | |
Excel Macro to Extract Specific Columns to a New Tab | 4 | 20 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
10 Experts available now in Live!