Counting Consecutive Zeroes

[ststern45]

Hi everyone,

I have this formula that will count how many consecutive zeroes appear at the bottom of a range:

=MAX((U20:U219<>"")*(ROW(U20:U219)))-MAX((U20:U219<>0)*(U20:U219<>"")*(ROW(U20:U219)))

How do I change the formula where it counts from the beginning of a range. For example,

U20 = 0
U21 = 0
U22 = 0
U23 = 1 (Note: can be any number other than 0 (zero)

So the correct calculation would be 3 (3 consecutive zeroes)

Thanks in advance!!

 

[Peatawn]

In your screen capture I only see 0's, 1's, 2's and 3's. Is 3 the highest number you can get in this table? I might have a solution to test if that is the case.

 

[Peatawn]

Actually I took a closer more attentive look at your screen capture and noticed that the highest value is 6. So I worked out this formula, works great on my end (and I tested every possibility). If however, it's possible that the highest value is greater than 6, the formula will need to be elongated.

=MIN(IF(COUNTIF(U20:U219;1);MATCH(1;U20:U219;0);99);IF(COUNTIF(U20:U219;2);MATCH(2;U20:U219;0);99);IF(COUNTIF(U20:U219;3);MATCH(3;U20:U219;0);99);IF(COUNTIF(U20:U219;4);MATCH(4;U20:U219;0);99);IF(COUNTIF(U20:U219;5);MATCH(5;U20:U219;0);99);IF(COUNTIF(U20:U219;6);MATCH(6;U20:U219;0);99))-1

Say the highest possible value is 7, then you will need to add the following before the ")-1" at the end of the formula:

";IF(COUNTIF(U20:U219;7);MATCH(7;U20:U219;0);99)"

And if the highest possible value is 8, then add it again, but replacing the numbers 7 (in red) by 8's.

If the highest possible value could be virtually anything (such as 2976), then I'll have to rethink the formula.

Please note that on my computer the delimiter is set to semi-colon (, your setting might be commas, hence you might need to replace my semi-colons with commas.

Hope this helps,
Peatawn

 

[JB_Scotland]

In V20 enter:

=IF(SUM(U$20:U20)=0,ROW(U20)-19,0)

Copy down to V219. In the cell you want your answer use =MAX(V20:V219)

 

[L. Howard]

Try this. I started out trying to write the code and had to ask a few question to get it to work. It became such a re-write that thanks go to Claus of the MS Public forum. I am just the broker here.

A couple of rules, if you have blanks in the countable consecutive zeros, the blanks are counted as a zero.
If the entire column is blanks, it returns a 1 in cell 20.

Regards,
Howard

Option Explicit

Sub CountConsZeros()
Dim rngC As Range
Dim i As Long
Dim LRow As Long
For Each rngC In Range("R26:AK26")
   If rngC = 0 Then
      Cells(20, rngC.Column) = 1
      LRow = Cells(Rows.Count, rngC.Column).End(xlUp).Row
      For i = 27 To LRow
         If Cells(i, rngC.Column) = 0 Then
             Cells(20, rngC.Column) = _
               Cells(20, rngC.Column) + 1
         Else
            Exit For
         End If
      Next i
   Else
       Cells(20, rngC.Column) = 0
   End If
Next rngC
End Sub

 

[barry houdini]

Assuming the ranges are fully populated as per your screenshot try this formula

=MATCH(TRUE,INDEX(U20:U219<>0,0),0)-1

 

[ststern45]

Thank you everyone for all your help. Barry your formula worked to perfection. I did not try the other suggestions. I appreciate everyone's help.

Thank you!!