Counting rows between each specific value

[colin5392]

I have a column of 1000+ numbers, but I want to be able to count the number of rows between each entry containing the value 1, e.g.

1 <--
15
25
3
5
2
1 <--
22
14
15
1 <--

How do I count those rows automatically please? Can anyone help?

 

[wideboydixon]

Not sure how efficient this will be based on how big you data set it but you could use this:

Book1
	A	B
1	1
2	15
3	25
4	3
5	5
6	2
7	1	5
8	22
9	14
10	15
11	1	3
Sheet3

Cell Formulas
Range	Formula
B2	=IF($A2=1,ROW()-LOOKUP(2,1/($A$1:$A1=1),ROW($A$1:$A1))-1,"")

WBD

 

[colin5392]

Not sure how efficient this will be based on how big you data set it but you could use this:

	A	B
1	1
2	15
3	25
4	3
5	5
6	2
7	1	5
8	22
9	14
10	15
11	1	3

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula B2 =IF($A2=1,ROW()-LOOKUP(2,1/($A$1:$A1=1),ROW($A$1:$A1))-1,"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

WBD

Thanks, I'll give it a go