How to group and count

[gkintl]

<article>

Hi, I would like to the formula to count if there is a "1" in the cell and stop if it encounters a "0". Again, it should start counting from 1, when it comes across a "1" in next cell.

It should look like this

A 1
B 1 2
C 0 0 <<Result
D 1 1
E 0 0
F 1
G 1
H 1 3
J 0 0

Can anyone help me with the formula? rgds,​

</article>

 

[kvsrinivasamurthy]

If Data starts From A2 and goes down, Formula in B2, then dragdown.

ARRAY formula is used
=IF($A2=0,0,IF($A3=0,IFERROR(ROWS($A$2:$A2)-MATCH(2,1/($A$2:$A2=0),1),ROWS($A$2:$A2)),""))

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets by excel.

 

[Peter_SSs]

In B2, copied down

Book1
	A	B
1	Numbers	Count
2	1
3	1	2
4	0	0
5	1	1
6	0	0
7	1
8	1
9	1	3
10	0	0
Count

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

 

[gkintl]

Hello, Thanks for the assistance.

 

[Peter_SSs]

Hello, Thanks for the assistance.

You are welcome.