多重复值列条件校验：Excel公式需求咨询

Got it, let's figure out the right Excel formula for your scenario. Here's a straightforward solution tailored to your needs:

First, let's clarify your requirement: You want to flag rows in column AX where two conditions are met:

1. The current row's job status in column P is n (uncompleted)
2. There's at least one other row with the same order number that has Y marked in column P (completed)

Assuming your order numbers are stored in column A (replace this with your actual order number column if it's different), use this formula in cell AX1, then drag it down to apply to all rows:

=IF(AND(P1="n", COUNTIFS(A:A, A1, P:P, "Y")>0), "Needs Follow-Up", "")

Breakdown of the formula: #

• COUNTIFS(A:A, A1, P:P, "Y")>0: This counts how many rows share the same order number as the current row (A1) and have Y in column P. If the count is greater than 0, it means there's at least one completed entry for this order.
• P1="n": Checks if the current row's job status is uncompleted (n).
• AND(...): Ensures both conditions are true before returning your annotation text. I used "Needs Follow-Up" as an example—feel free to replace this with any label you prefer (like "待跟进" or "Review Required").
• If either condition isn't met, the formula returns an empty cell ("").

Optional: Case-Sensitive Version #

If you need strict case matching (e.g., only recognize uppercase Y and lowercase n, ignoring y or N), use this modified formula with SUMPRODUCT and EXACT:

=IF(AND(EXACT(P1,"n"), SUMPRODUCT(--(A:A=A1), --(EXACT(P:P,"Y")))>0), "Needs Follow-Up", "")

内容的提问来源于stack exchange，提问作者Michael Phillips