count the number of consecutive values >=2

I would like to count the number of consecutive values >=2 in a row.

Eg:-
1 2 3 2 3 2 2 2 2 1 1 1 3 3 2 2 1 1

The returned value would be 8, because the 2nd to the 9th figure are all greater or equal to 2

OR

1 2 3 1 2 1 3 2 1 3 2 3 4 3 1 2 3 1

The returned value would be 5, because the 10th to the 14th figure are all greater or equal to 2

Originally Posted by jomuir

I would like to count the number of consecutive values >=2 in a row.

Eg:-
1 2 3 2 3 2 2 2 2 1 1 1 3 3 2 2 1 1

The returned value would be 8, because the 2nd to the 9th figure are all greater or equal to 2

isnt the answer 12 and not 8?

=COUNTIF(A2:R2,">=2")

Oh sorry - you said consecutive!

I assume you want the maximum number of consecutive values >= 2. For that try this formula

=MAX(FREQUENCY(IF(A2:R2>=2,COLUMN(A2:R2)),IF(A2:R2<2,COLUMN(A2:R2))))

confirmed with CTRL+SHIFT+ENTER

No, that gives the answer 6 - it appears to coult all the numbers less than 2.

so if I had 1 1 1 1 1 1 1 1 1 1 1 2 2 2 3 1 1 1 then the answer I am looking for is 4, based on the numbers 2, 2, 2 & 3 being >=2 and consecutive

Hi,

The solution DDL posted works for me - did you give it a try?