Thread: Running count

I have a column where i have a number of 0's that appear and 1's

looks like this

0
0
0
1
0
1
0
1
1
0
0

I need to count how many 0's occur before each 1.

Anyone know a formula?!?

Basically each 0 represents a unit of time, each 1 reprsents a lifetime..

by the end of this formula the lifetime should reset back to one and not 1,2,3,4,5,6

In B1, have =IF(A1=0,"",1). In B2 and copied down as needed, put =IF(A2=0,"",COUNTIF($A$1:A1,0)-SUM($B$1:B1)). Note that your data will have to end with a 1 for all 0s to be counted. If you want the last to be counted regardless, you could modify B2 to be =IF(A2=0,IF(COUNT(A:A)>ROWS(A$1:A2),"",1+COUNTIF($A$1:A1,0)-SUM($B$1:B1)),COUNTIF($A$1:A1,0)-SUM($B$1:B1))

Here is without helper column: