Count Max Consecutive duplicate column values, ignoring blank cells

[ToddB68]

Example: Count the max consecutive number of "S" entries in the following data which is stored in column A:

F
F
S
F
F

S
S
S
F
S
F
S
S

S
S
F

I tried using the following function, but it returns a 3 for the example data and I am expecting a 4..

=MAX(FREQUENCY(IF(A1:A100="S",ROW(1:100)),IF(A1:A100<>"S",ROW(1:100))))

Can someone help show how to ignore the blank cells??

Thank you,

Todd.

 

[barry houdini]

Hello Todd, welcome to MrExcel,

Do you only have "F", "S" and blanks in the data? If so try changing to

=MAX(FREQUENCY(IF(A1:A100="S",ROW(1:100)),IF(A1:A100="F",ROW(1:100))))

 

[pgc01]

Hi Todd
Welcome to the board

You can also add the test to the empty cells in your formula:

=MAX(FREQUENCY(IF(A1:A100="S",ROW(1:100)),IF(A1:A100<>"S",IF(A1:A100<>"",ROW(1:100)))))

 

[ToddB68]

Thank you for the quick response. I am good to go.