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

tuqiuchiaa

New Member
Joined
Mar 12, 2018
Messages
3
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

ABCDEFResult
2120003
002032002
00110012
102312211

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,949
Office Version
365
Platform
Windows
Welcome to the Board!

I used VBA to create my own function to do it. Here is what it looks like:
Code:
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

New Member
Joined
Mar 12, 2018
Messages
3
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,949
Office Version
365
Platform
Windows
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

New Member
Joined
Mar 12, 2018
Messages
3
You know what. My bad. This should work, thank you so much.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,949
Office Version
365
Platform
Windows
You are welcome.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,165
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,949
Office Version
365
Platform
Windows
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).
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,800
Office Version
2010
Platform
Windows
Welcome to the Board!

I used VBA to create my own function to do it. Here is what it looks like:
Code:
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...
Code:
[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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,949
Office Version
365
Platform
Windows
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:
Function Consec(myRange As Range) As Long
Consec = Len(Split(Application.Trim(Join(Evaluate("IF(" & myRange.Address & "=0,"" "",""X"")"), "")))(0))
End Function

<tbody>
</tbody>

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).
 

Watch MrExcel Video

Forum statistics

Threads
1,095,176
Messages
5,442,836
Members
405,201
Latest member
kashyap44

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top