Consecutive cells count

**moonbreakker** · 10-12-2015, 04:29 PM

Hello,

I thought this should be simple and googled a lot.

1.A
2.B
3.A
4.A
5.B
6.A
7.A
8.B
9.A
10.A

I need consecutive count for A which is 3 in this case.. rows 3,4 and 6,7..9,10
I tried something like =IF(L2=L1,M1+1,1) but it gives me 1,2,3,4.. But i need total count as in above eg, i need 3 in some cell.

Thank you.

**davesexcel** · 10-12-2015, 05:04 PM

Not sure what you mean by 3, where does 3 come from?

**Tony Valko** · 10-12-2015, 06:02 PM

If you mean the result should be 4 then try this array formula**:

=SUM(IF(FREQUENCY(IF(A1:A10="A",ROW(A1:A10)),IF(A1:A10<>"A",ROW(A1:A10))),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**Tony Valko** · 10-12-2015, 06:46 PM

After reading your post again I now think I understand.

You want to count how many times "A" appears in consecutive cells.

Try this array formula**:

=SUM(IF(FREQUENCY(IF(A1:A10="A",ROW(A1:A10)),IF(A1:A10<>"A",ROW(A1:A10)))>1,1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**MarvinP** · 10-12-2015, 09:45 PM

Hi moonbreakker,

I think you want something different than the answers above. I think you want to know how many times the column cells are the same. You are correct it is hard to explain. I've created a Helper column and a formula that I think you want in the attached.