Count Consecutive Repeating Numbers in One Column

Hi, looking for some assistance please.

I’m trying to apply a formula DonkeyOte provided in a different thread to numbers in this case, but it's pretty much the same thing.

In the current sheet, I’m trying to Count the number of times a “1” or “2” appears consecutively in Column H and output my results to either Column J (if looking for a "1") or Column K (if looking for a "2"). The problem is the formula gives me the incorrect result for the first “2” or “1” it hits in my series. All other values are correct though.

So an example, if I'm trying to find all the 1's in Column H and I output my results to Column J, the formula I'm using is:

=IF(OR($H3<>J$1,$H4=$H3),"",COUNTIF($H$3:$H3,J$1)-SUM(J$1:J1))

The first "1" it hits in Column H, my result in Column J is a "0", where it should be a 1. For the first "2" in Column H, the result in Column K is a "-1" where it should be a 1.

I've attached a sample of my worksheet in case it's useful.

Any help on this would really be appreciated.

Hi,
You are including the $J$1 cell in the -SUM() element of the formula. J1 is of course the value you are testing for. Change the J3 formula to read

Please Login or Register  to view this content.

and copy down. Ditto with column K.

Regards