Count number of zeros that are within a range of consecutive zeros

Re: Count number of zeros that are within a range of consecutive zeros

Try this UDF

Function ZeroCount(r As Range) As Integer
    Dim x, i As Long, ii As Long, icount As Long
        x = r.Value
        
        For i = 1 To UBound(x, 2)
            If x(1, i) = 0 Then
                ii = 0
                Do Until x(1, i) = 1 Or i = UBound(x, 2)
                    ii = ii + 1: i = i + 1
                Loop
                If ii > 17 Then
                    ZeroCount = ZeroCount + ii
                    If i = UBound(x, 2) Then ZeroCount = ZeroCount + 1
                End If
            End If
        Next
        
End Function

Then in cell AW2 put this formula

=zerocount(A2:AV2)

and copy down as far as needed.

Note the count for Row 5 should be 43, not 42 as shown in your sample file.

Re: Count number of zeros that are within a range of consecutive zeros

If you NEED an answer that does not use VBA at all, and have spare columns, this will do it:
Cell AY2 = "=IF(SUM(A2:R2)=0,IF(AX2>0,1,18),0)" fill across to CC2 and then down to bottom of table.
Then a simple "=SUM(AY2:CC2)" in AW2 (and filled down) will provide accurate counts.