Count First set of Consecutive Values

**sushi637** · 05-19-2010, 02:27 PM

Here is what my row looks like:

IDLE IDLE IDLE IDLE IDLE DOWN DOWN DOWN IDLE IDLE IDLE QUAL QUAL QUAL

I need a formula which will look at the first value:

IDLE IDLE IDLE IDLE IDLE DOWN DOWN DOWN IDLE IDLE IDLE QUAL QUAL QUAL

'IDLE' in this case. I then need the forumula to count the number of consectutive 'IDLE' values in the first string of consectutive 'IDLE' values.

IDLE IDLE IDLE IDLE IDLE DOWN DOWN DOWN IDLE IDLE IDLE QUAL QUAL QUAL

The formula should give me 5 in this case because it took the first value and saw how many times it repeated before hitting the next value of 'DOWN'

Please let me know if I need to be more clear. Thanks!

**NBVC** · 05-19-2010, 02:32 PM

If your items in are in say, A1:N1, try:

=COUNTA(A1:INDEX(A1:N1,MATCH(TRUE,INDEX(A1:N1<>A1,0),0)-1))

**DonkeyOte** · 05-19-2010, 03:00 PM

or

=MATCH(TRUE,INDEX(A1:N1<>A1,0),0)-1

both the above and NBVC's are open to error of course should all values be the same as that in A1 (ie you might want to validate that first off).