# counting cells between specific values

This is a discussion on counting cells between specific values within the Excel Questions forums, part of the Question Forums category; If I have a column of numbers in which #1 may appear numerous times, I need to count the number ...

1. ## counting cells between specific values

If I have a column of numbers in which #1 may appear numerous times, I need to count the number of cells between each appearance of #1

2. ## Re: counting cells between specific values

I'm not one of the site experts... but I had an idea that might work for you using Match

If you have your column of numbers in Column A, enter the formula =MATCH(1,A2:A19,0)-1 in Column B and copy to the end of your list.

If you look at the results in Column B you will see that each value of 1 in Column A has a result of 0 in Column B. The value immediately below the 0 in Column B is the number of cells between two instances of 1's in Column A. So I changed the MATCH formula into an IF statement.....

=IF((MATCH(1,A1:A18,0)-1)=0,B2,"")

I don't know if there is a way to turn this around so your count total is at the bottom of each group. Hope this gives you some ideas at least.

3. ## Re: counting cells between specific values

I appreciate your reply. Here is an example of what I am trying to accomplish.

4. ## Re: counting cells between specific values

 Example 1 Example 2 A B C A B C ideal ideal Formula Formula Formula Formula Data Column Column Data Column Column 1 1 0 0 1 4 3 2 4 4 2 6 5 3 10 4 3 3 2 4 3 1 4 1 3 0 5 6 2 5 9 6 1 4 6 7 7 0 7 5 8 9 8 15 9 3 9 1 5 10 1 4 10 3 11 6 11 20 12 1 1 12 1 2 13 4 13 1 0 14 12 14 3 15 1 2 15 7

5. ## Re: counting cells between specific values

Afraid that is beyond my skill set. I hope someone can help.

6. ## Re: counting cells between specific values

in B1 copy down

=IFERROR(INDEX(\$C\$1:\$C\$5,IF(A1=1,COUNTIF(\$A\$1:A1,1),"")),"")

7. ## Re: counting cells between specific values

This recognizes each "1" and places a zero across from each in column B but does not count the number of events between each 1
Thanks

8. ## Re: counting cells between specific values

Excel 2012
AB
1DataFormula
22
34
410
513
66
711
80
911
1010
1110
126
1311
144
1512
1612

xl5galry.xls

Worksheet Formulas
CellFormula
B2=IF(A2=1,IF(A2=1,COUNTIF(\$A\$2:A2,"<>"&1),"")-SUM(\$B\$1:B1),"")

9. ## Re: counting cells between specific values

Words maybe powerful, but I cannot find any strong enough to express my thanks for this wonderful formula. It will save me many hours.

Thank you very much