How to identify per row sequence of 5 cells containing number "8"

[M209]

Hello -
I need to identify and count , on a row by row basis, each occurrence of a sequence of five cells that each contain the number "8" . I am looking for the results to be something like: Row 1 = 2, Row 2 = 3, Row 3 = 1, Row 4 = 2 etc... with the "= n" being the number of times in that row that there exists a sequence of five cells that contain the number "8". Seems like it should be simple(?) but so far I can not find a solution. Thank you!

 

[T. Valko]

Hello -
I need to identify and count , on a row by row basis, each occurrence of a sequence of five cells that each contain the number "8" . I am looking for the results to be something like: Row 1 = 2, Row 2 = 3, Row 3 = 1, Row 4 = 2 etc... with the "= n" being the number of times in that row that there exists a sequence of five cells that contain the number "8". Seems like it should be simple(?) but so far I can not find a solution. Thank you!

Try this array formula**:

=SUM(IF(FREQUENCY(IF(A1:R1=8,COLUMN(A1:R1)),IF(A1:R1<>8,COLUMN(A1:R1)))=5,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.

Copy down as needed. Adjust the ranges to suit.

 

[M209]

Brilliant! Thank you so much !!

 

[T. Valko]

Brilliant! Thank you so much !!

You're welcome. Thanks for the feedback!

 

[M209]

How about a "bonus question"...?

The wonderful array formula you provided will count sequences of exactly five cells - no more, no less - that contain the number "8". Is there a way to change the formula so it counts sequences of 5 or more cells? For example, if there is a sequence of 10 cells in a row and each cell contains the number "8", can we modify the formula to count this as well?

 

[T. Valko]

How about a "bonus question"...?

The wonderful array formula you provided will count sequences of exactly five cells - no more, no less - that contain the number "8". Is there a way to change the formula so it counts sequences of 5 or more cells? For example, if there is a sequence of 10 cells in a row and each cell contains the number "8", can we modify the formula to count this as well?

Just change =5 to >=5.

=SUM(IF(FREQUENCY(IF(A1:R1=8,COLUMN(A1:R1)),IF(A1:R1<>8,COLUMN(A1:R1)))>=5,1))

Don't forget...array enter!

 

[M209]

You are the best !! Thank you!

 

[T. Valko]

You are the best !! Thank you!

You're welcome!