# Count consecutive > zero, ignore zero count consecutive > zero next cell

1. ## Count consecutive > zero, ignore zero count consecutive > zero next cell

Hi All,

Hope you are well,

So basically i come cap in hand to your superior knowledge,

In the data range (B2:DK2 Contains a range of numbers (counts of instances by day) between 0 & 5.

The below array formula counts the zeroes, and when dragged across moves onto the next group of zeroes.

=IFERROR(SMALL(IF(\$B2:\$DK2>0,COLUMN(\$B2:\$DK2)),COLUMN(A:A)+1)-SMALL(IF(\$B2:\$DK2>0,COLUMN(\$B2:\$DK2)),COLUMN(A:A))-1,"")

Im ultimately after the reverse, so for example -

00252500002524210000012512201010102020002222 should show(in consecutive cells), 4,6,6,1,1,1,1,1,4.

2. ## Re: Count consecutive > zero, ignore zero count consecutive > zero next cell

Hi karl and welcome to the forum,

I have an answer without a single formula, although it may be possible... Here is the method. Concat() all those numbers into a long string of digits. Replace all the "0" with a space (Use the Substitute() formula for that). Then (and here is the secret) Trim() that string reducing all double spaces to a single space. Now take that string and do a Text To Columns. Put a formula under that row of Len() of numbers between zeros and Boom (drop the mic) your answer. See the attached. I may have left out a Paste as Value in the next to last step.
Len of run of non zero cells.xlsx

3. ## Re: Count consecutive > zero, ignore zero count consecutive > zero next cell

So here's my way of thinking about this....I would eventually use 'Convert text to column wizzard' ~ Delimited: "Other = 0" and check the box 'Treat consecutive delimiters as one [ this is in the Ribbon, under DATA / Text to Columns. Before that I have a Function in VBA that reverses the string. Once the string is reversed, I use the Text to column wizzard to separate the numbers by the character '0' (which will put the single character or cluster of numbers into their own cells...then it's just a matter of concatenating them together. Reverse is available in vba code...the function is ~
``Please Login or Register  to view this content.``
I've attached sheet too....hope this helps.

4. ## Re: Count consecutive > zero, ignore zero count consecutive > zero next cell

OK Karl,

Here is a single formula that will give you the answer.
Formula:
`Please Login or Register  to view this content.`

See the formula and example in the attached
Len of run of non zero cells single formula.xlsx

5. ## Re: Count consecutive > zero, ignore zero count consecutive > zero next cell

Hi, there!

FREQUENCY(data_array,bins_array) function does the trick here.

Supposing A1=00252500002524210000012512201010102020002222 (text)
Formula:
`Please Login or Register  to view this content.`

grid is defiened Name for simplicity in the Name Manager
Formula:
`Please Login or Register  to view this content.`

Then the result array is {0;0;4;0;0;0;6;0;0;0;0;6;1;1;1;1;1;0;0;4}

Pls refer to the attched file.

Cheers

6. ## Re: Count consecutive > zero, ignore zero count consecutive > zero next cell

Thanks All,

They all work very well, im just determining the best one to use for the purposes im using it for.

Really appreciate it.

Kind Regards

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1