Consecutive Cells
 

I have used various formulas on this site to count consecutive occurences
fora piece of data, but to no avail. Can someone please help? In column
"I" I have a bunch of O's and U's. I want to be able to count how many
times O appreared 3 consecutive times, 4 consecutive times etc.. up to 10.
My column run down to 162 on some pages and less on others, if this matters?
Also is there a way to do a check based on a hiearchy of variables?

--
Message posted via http://www.officekb.com

"Stat Guy via OfficeKB.com" wrote...
I have used various formulas on this site to count consecutive occurences
fora piece of data, but to no avail. Can someone please help? In column
"I" I have a bunch of O's and U's. I want to be able to count how many
times O appreared 3 consecutive times, 4 consecutive times etc.. up to 10.
My column run down to 162 on some pages and less on others, if this
matters?
Also is there a way to do a check based on a hiearchy of variables?

While it may be possible to do this in a single formula, it'd be so
convoluted and inefficient that it wouldn't be worth it.

If your O's and U's were in, say, A2:A101, you could use formulas in B2:B101
to count the consecutive instances.

B2:
=IF(A3<A2,1,"")

B3:
=IF(A4<A3,ROWS(B$2:B3)-IF(COUNT(B$2:B2),
LOOKUP(2,1/ISNUMBER(B$2:B2),ROW(B$2:B2)-1),0),"")

Fill B3 down into B4:B101. Then count the number of 4 consecutive instances
of O using

=SUMPRODUCT((A2:A101="O")*(B2:B101=4))