How to check consecutive rows and flag if not blank

Stefano Ferretti

Hi All,

I have a sheet where I'm tracking utilization on a weekly basis.

I need to check if utilization gets over a threshold on 3 consecutive rows.

but I'm not sure how to do this.

In the example below Row 7 should have something on the "Alert?" Column, as it passed the threshold 3 times in a row.

Paul Newcome

You would start by inserting an Auto-Number type column (called "Auto" in this example). Next you would set up a text/number column (called "Row" in this example) with this column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then to flag the Alert column you would use something along the lines of...

=IF(COUNTIFS(Row:Row, AND(@cell>= Row@row - 2, @cell<= Row@row), Threshold:Threshold, @cell = "High") = 3, "ALERT")

Paul Newcome

You would start by inserting an Auto-Number type column (called "Auto" in this example). Next you would set up a text/number column (called "Row" in this example) with this column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then to flag the Alert column you would use something along the lines of...

=IF(COUNTIFS(Row:Row, AND(@cell>= Row@row - 2, @cell<= Row@row), Threshold:Threshold, @cell = "High") = 3, "ALERT")

Stefano Ferretti

Thank you very much! it worked like a charm

Paul Newcome

Happy to help. 👍️