Formula to count rows equals to zero but without mentioning its range

Please, I need a formula to count how many cells are equal to zero before some.
This is for a group of lateral columns.
I will drag the formula, so that it shows this information again and again until the last row:

A B
1
0
0
1 2 (there are 2 rows equal to zero until the previous number 1)
0
0
0
1 3 (there are 3 rows equal to zero until the previous number 1)
0
1 1 (there is 1 row equal to zero until the previous number 1)

Something like (in B2):
=IF(B2<>0,(COUNT HOW MANY ROWS ARE EQUAL TO ZERO UNTILL PREVIOUS NUMBER<>0),"")

But I don't want to define how many rows there can be between the numbers different of 0, could be 1 or 500 or more.
How to do this without mentioning its range?

Originally Posted by Tony Valko

Enter a 1 in cell A2.

Now do you get the warning?

No.....???

Yes....???

max iterations 100
max change 0.01

I don't remember ever enabling it. I do know what iterations are... but I always thought that it was enabled by default when I installed. Should I disable it?

Tony, I've tried your formula and it works fine:
=IF(A2=1,"",IF(OR(A2<>A3,A3=""),COUNTIF(A$2:A2,0)-SUM(B$1:B1),""))

Thank you.
But, I realise that it could be better for me if slightly modfifed.

I've tried this:
=IF(A2=1,"",IF(OR(A2<>A3,A3=""),(COUNTIF(A$2:A2,0)+1)-SUM(B$1:B1),""))

Or:
=IF(A2=1,"",IF(OR(A2<>A3,A3=""),(COUNTIF(A$2:A2,0)-(SUM(B$1:B1)-1),""))

To have the numbers in column B plus 1 (the first number doesn't have to be exact, the most important are the last ones), like:
0.....
0.....
0.....
1.....4
0.....
0.....
1.....3
0.....
0.....2

But that's not working.
If you could show how to alterate the formula.

Originally Posted by dualaudio454252

...
If you could show how to alterate the formula.

Try this one in B1 down...

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


...and as Tony brings up, this is to count immediately preceding zero(es).

Please, I have another thread:
http://www.excelforum.com/excel-form...02#post3330702

But I decided to start a new thread.
Cause that's another point and I've spent 1 day searching into this and another forums, and trying to change some solutions to what I need.

I need to know the distance between 2 not empty cells in the same column.
The first cell may have a text (or number) or not.
And the distance may vary from 1 to 5 to 500 or more.
Like:

A B

peanut (this one don't show a number, cause there's no other occurrence before)


tomato 3






banana 7



basket 4

That's it.

use this in B2 (if your data start from A1, anyway one row down):

Formula:

Please Login or Register  to view this content.

The answer is here:

http://www.excelforum.com/excel-form...27#post3331627

I guessed I was doing exactly what should be better, but thank you for the advice!

I guess this way is more appropriate to what I really need:

http://www.excelforum.com/excel-form...51#post3333551

Thank you for your help!