Excel连续3列单元格检查：大于5的连续值≥3个时输出YES

Hey there! Let's work through this Excel problem you're tackling. You need to check a set of 3 consecutive columns and output "YES" if there's at least one sequence of 3 consecutive numerical values (all greater than 5, like your example of 6, 7, 7.2). I'll cover a few common scenarios you might be dealing with, so you can pick the one that fits your needs.

Scenario 1: Check a single row across 3 consecutive columns #

If you're focused on individual rows (e.g., cells A1, B1, C1) and want to confirm all three are numbers above 5, drop this formula into an adjacent cell (like D1):

=IF(AND(ISNUMBER(A1), ISNUMBER(B1), ISNUMBER(C1), A1>5, B1>5, C1>5), "YES", "NO")

Here's what each part does:

• ISNUMBER() ensures we only count actual numerical values (ignoring text, blanks, or errors)
• The >5 checks confirm each number meets your threshold
• AND() ties it all together—only if every condition is true do we get "YES"; otherwise, it returns "NO".

Scenario 2: Check for any valid sequence in a larger 3-column range #

If you have multiple rows in your 3 columns (say A1:C10) and want to know if there's any valid sequence (either vertical in a column or horizontal in a row), use these options:

Vertical sequences (3 consecutive rows in the same column) #

This formula checks each of the 3 columns for runs of 3 consecutive numbers >5. If any column has such a run, it outputs "YES":

=IF(OR(
    SUMPRODUCT(--(ISNUMBER(A1:A8))*(A1:A8>5)*(ISNUMBER(A2:A9))*(A2:A9>5)*(ISNUMBER(A3:A10))*(A3:A10>5))>0,
    SUMPRODUCT(--(ISNUMBER(B1:B8))*(B1:B8>5)*(ISNUMBER(B2:B9))*(B2:B9>5)*(ISNUMBER(B3:B10))*(B3:B10>5))>0,
    SUMPRODUCT(--(ISNUMBER(C1:C8))*(C1:C8>5)*(ISNUMBER(C2:C9))*(C2:C9>5)*(ISNUMBER(C3:C10))*(C3:C10>5))>0
), "YES", "NO")

The SUMPRODUCT() counts how many valid 3-row sequences exist in each column. If any count is greater than 0, OR() triggers the "YES".

Horizontal sequences (any row where all 3 column cells are >5) #

To check if any row in your 3-column range has all three cells as numbers >5, use this:

=IF(SUMPRODUCT(--(ISNUMBER(A1:A10))*(A1:A10>5)*(ISNUMBER(B1:B10))*(B1:B10>5)*(ISNUMBER(C1:C10))*(C1:C10>5))>=1, "YES", "NO")

This counts the number of rows that meet all criteria. If there's at least one, you get "YES".

Bonus: Check for consecutive 3-column blocks in a wider row #

If you have a row with more than 3 columns (like A1:E1) and want to check if any 3 consecutive columns in that row have all numbers >5, use this array formula (modern Excel handles it automatically; older versions need Ctrl+Shift+Enter):

=IF(MAX(IF((ISNUMBER(A1:C1)*(A1:C1>5))*(ISNUMBER(B1:D1)*(B1:D1>5))*(ISNUMBER(C1:E1)*(C1:E1>5)), 1, 0))=1, "YES", "NO")

This scans every triplet of consecutive columns in the row and returns "YES" if any triplet passes the check.

内容的提问来源于stack exchange，提问作者A Zaman Sagor