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

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]22[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IF($A2=1,ROW()-LOOKUP(2,1/($A$1:$A1=1),ROW($A$1:$A1))-1,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



WBD

Thanks, I'll give it a go