How to count until a certain value?

Question

I am just using formulas in excel and was wondering how you could count all the 0s until a 1 is reached, and then start the process over again. Would this be possible with just formulas?

Right now I am trying to use,

=COUNTIF(INDIRECT(F2:F28,0)),1)

... but it does not seem to work.

Data:

0
1
0
1
1
0
0
0
0
0
0
1
0
1
0

Answer 1

What you're describing here is known as a (Do) While loop in VBA.
Admittedly, something VBA is much better equipped to handle than a formula - literally needs few lines of code. Should be something you should consider looking into

You can technically fake a Do While with a formula. But this is generally a practice I would not exactly recommend.

Under presumption you data input starts in Cell F2 then formula in adjacent Column G2
=IF(F2=0, SUM(G1, 1), 0) and drag the formula accross. Produces the expected result

Answer 2

I have a solution, but it's not very elegant. Maybe someone else can improve on it. But here it is:

Assuming your data is in column A.

1) In B1 enter a 1 if A1 is a 0 or 0 if A1 is a 1.

2) In B2 enter the formula =IF(A2=0,B1+1,0)

3) Drag the above formula all the way down

4) In C1 enter the formula =IF(B1>B2,B1,"")

5) Drag the above formula all the way down

6) Column C should now have all of the counts of zeroes.

Answer 3

In B2 enter:

=IF(OR(A2=0,A1=1),"",COUNTIF($A$1:A1,0)-SUM($B$1:B1))

and copy downward:

Answer 4

You can use the following formula in column B:

=IF(A3=1,COUNTIF(A$1:A2,0)-SUM(B$1:B1),"")

starting in row 2 and drag it down.

Answer 5

This seems to work according to your sample data,

=COUNTIF(F2:INDEX(F:F, IFERROR(AGGREGATE(14, 7, ROW($1:2)/(F$1:F2=1), 1), ROW(F2))), 0)

Answer 6

Put 1 in the cell next to the first 0 (In my example that's in A1, so i've put 1 in B1);
Then try this =IF(A2=A1;B1+1;1)