Count the first non-zero Consecutive Cells until another 0 appears

[tuqiuchiaa]

Hi,
I'm trying to figure out a way to count the number of cells in the first non-zero Consecutive cells.
Been reading all the posts about consecutive cells posts, but couldn't get anything working.
Could anyone help? Thanks in advance!
The desired results are listed below

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD][/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD]32[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]231[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Welcome to the Board!

I used VBA to create my own function to do it. Here is what it looks like:

Function Consec(myRange As Range) As Long
'   Count the first non-zero consecutive cells until another 0 appears

    Dim cell As Range
    Dim ct As Long
    
    For Each cell In myRange
        If cell = 0 Then
            If ct > 0 Then Exit For
        Else
            ct = ct + 1
        End If
    Next cell
    
    Consec = ct
    
End Function

So then you would just use it like any other function in Excel, i.e.
=Consec(A2:F2)

 

[tuqiuchiaa]

Hi Joe, Thank you very much for the reply. I can do it in VBA too, but the reason why I want to stick with Excel Formula is that no one else in the team knows VBA, so Excel formulas is better in term of re-usability of this sheet and for the sake of saving me the effort of changing VBA code for them every week.
Thanks,
Ben

 

[Joe4]

Why would you need to change the VBA code every week?
The Function I gave you is very generic, and shouldn't need any updating, just like any other Excel function.
Also, no one needs to know/understand VBA in order to use that function. It really just is "plug and play".

I am sure that there probably is a formulaic approach that will work, but those type of array formulas really are not my specialty (which is why I usually go the VBA route).
Maybe someone else will be able to provide a formulaic solution.

 

[tuqiuchiaa]

You know what. My bad. This should work, thank you so much.

 

[Joe4]

You are welcome.

 

[Aladin Akyurek]

In H2 control+shift+enter, not just enter, and copy down:

=MAX(FREQUENCY(IF(ISNUMBER(A2:F2),IF(A2:F2=0,COLUMN(A2:F2))),IF(1-(A2:F2=0),COLUMN(A2:F2))))

 

[Joe4]

In H2 control+shift+enter, not just enter, and copy down:

=MAX(FREQUENCY(IF(ISNUMBER(A2:F2),IF(A2:F2=0,COLUMN(A2:F2))),IF(1-(A2:F2=0),COLUMN(A2:F2))))

Aladin,

I came across an issue with this solution.
It works fine as long as there are BOTH zeroes and numbers.
But, if all six cells are 0, it will return a 6 (instead of 0).
And if all six cells are numbers other than 0, it will return 0 (instead of 6).

 

[Rick Rothstein]

Welcome to the Board!

I used VBA to create my own function to do it. Here is what it looks like:

Function Consec(myRange As Range) As Long
'   Count the first non-zero consecutive cells until another 0 appears

    Dim cell As Range
    Dim ct As Long
    
    For Each cell In myRange
        If cell = 0 Then
            If ct > 0 Then Exit For
        Else
            ct = ct + 1
        End If
    Next cell
    
    Consec = ct
    
End Function

So then you would just use it like any other function in Excel, i.e.
=Consec(A2:F2)

For those who might find this interesting, and assuming the range being given to this function is always a horizontal range of contiguous cells, the above UDF can be written as a one-liner...

[table="width: 500"]
[tr]
	[td]Function Consec(myRange As Range) As Long
  Consec = Len(Split(Application.Trim(Join(Evaluate("IF(" & myRange.Address & "=0,"" "",""X"")"), "")))(0))
End Function[/td]
[/tr]
[/table]

 

[Joe4]

For those who might find this interesting, and assuming the range being given to this function is always a horizontal range of contiguous cells, the above UDF can be written as a one-liner...
Code:
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Function Consec(myRange As Range) As Long
Consec = Len(Split(Application.Trim(Join(Evaluate("IF(" & myRange.Address & "=0,"" "",""X"")"), "")))(0))
End Function[/TD]
[/TR]
</tbody>[/TABLE]

I think you need to add some error handling code in there. That function blows up if all the cells are 0 (it returns the #VALUE ! error).