formula to count cells between same values

Question

hi everyone i am looking for a formula that would count cells between cells with same value for example Column A looking for the number between cells having 2 as value so first answer would be 10 as the next 2 apears 10 cells down then reset and count again and the result would be placed beside the 2 then 9 and so on and the formula would be placed on same sheet but in column CA &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; C2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;9 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;015 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;07 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;07 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;19 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;48 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;019 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;07 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;44 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;02 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;103 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;022 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;08 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;84 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;63 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;59 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;122 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;82 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;18 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;65 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;019 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;151 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;11hope someone can help me with that :)thank you for your interest and time

hansvogelaar · Answer

This works best if the data start in A2 (with a header in A1, for example).
In B2:
=IFERROR(ROW($A2)-XMATCH($A2, $A$1:$A1, , -1), 0)
Fill down.

Forum Discussion

formula to count cells between same values

1 Reply

Resources