Counting cells with number

[Keala]

I have issue in count cells which I hope you can suggest a solution for.

I have column with a mix of 0s and value >0. So I need to count number of zeros in between each number>0 and state it on the next column.
It looks like this and what I want to achieve:
1
0
0
0
0
12 count= 4
0
0
0
3 count=3
0
0
0
0
0
0
55 count=6
...

The number of zeros is different all the time, sometimes it is only 3 zeros then a number or sometime it is 55 zeros then a number. I could do this manually with count function but since it is more then 300000 rows it will be quite demanding.

All suggestions are appreciated, maybe a macro is not neccessary and a work sheet solution would do it?

 

[AlanY]

try this

Book1
	A	B
1	1
2	0
3	0
4	0
5	0
6	12	4
7	0
8	0
9	0
10	3	3
11	0
12	0
13	0
14	0
15	0
16	0
17	55	6
Sheet7

Cell Formulas
Range	Formula
B2	=IF(A2=0,"",COUNTIF($A$1:A2,0)-SUM($B$1:B1))

 

[Keala]

Thank you Alan for the suggestion, but unfortunately I get alert code:

"There's a problem with this formula.
Not trying to type a formula?
...
"

Just to make sure I'm clear, the numbers and position below is just as example. The column which this need to be applied on is about 30000 rows and the cell number of zeros and number>0 is undefined.

 

[AlanY]

try this

Book1
	A	B
1	1
2	0
3	0
4	0
5	0
6	12	4
7	0
8	0
9	0
10	3	3
11	0
12	0
13	0
14	0
15	0
16	0
17	55	6
Sheet7

Cell Formulas
Range	Formula
B2	=IF(A2=0,"",COUNTIF($A$1:A2,0)-SUM($B$1:B1))

have you copied the formula above to Cell B2 directly? it should work.

 

[Joe4]

It works fine for me.
If you set up the example like Alan shows, does it work on that?
If it does work on that example, but not on your data, then you are probably either making a typo or not quite applying it correctly to your situation.
(Also, your number are entered as numbers, and not text, right)?

 

[Keala]

Yes, I have both copied and typed it my self. None work. I'm using Excel 2016 and have comma as separator with two digit decimal numbers, if that matters?

 

[AlanY]

how about?

=IF(A2=0,"",COUNTIF($A$1[COLOR="#FF0000"];[/COLOR]A2,0)-SUM($B$1[COLOR="#FF0000"];[/COLOR]B1))

 

[Keala]

Unfortunately it does not work for me on the exactly same setup as the example either.

 

[Keala]

The output is the same, it highlight ||0,"",countif|| part of the command. So I did some tests and this =IF(A3=0;"";COUNTIF($A$1:A3;0)-SUM($B$1:B2)) does not pop a alert code but it does not fill any number either. Just a green arrow at the upper left corner on each cell?

 

[AlanY]

do the 2 formula below work for you?

Book1
	A	B
1	1
2	2
3	3
4
5	6	2
Sheet10

Cell Formulas
Range	Formula
A5	=SUM(A1:A3)
B5	=COUNTIF(A1:A3,">"&1)