在Excel中基于多条件为新列返回对应值的公式需求

Looks like you need to flag specific "issue" cases where the Overall status is Fail, but all Individual entries for the same Item are Pass—which matches your sample data perfectly. Here's how to build the formula:

Formula for Cell D2 (assuming your data starts at row 2) #

=IF(AND(B2="Fail", COUNTIFS(A:A, A2, C:C, "Fail")=0), "issue", "")

Breakdown of the Formula: #

• COUNTIFS(A:A, A2, C:C, "Fail"): Counts how many rows for the same Item (A2) have an Individual status of "Fail". If this equals 0, it means all Individuals for that Item are Pass.
• AND(B2="Fail", ...): Checks two conditions at once: the current row's Overall status is Fail, AND there are no Individual Fails for the Item.
• If both conditions are true, it returns "issue"; otherwise, it returns a blank cell.

Using Structured References (If Your Data is in an Excel Table) #

If you've formatted your data as an Excel Table (recommended for dynamic ranges), use this cleaner version:

=IF(AND([@Overall]="Fail", COUNTIFS([Item], [@Item], [Individual], "Fail")=0), "issue", "")

Verification Against Your Sample Data #

Let's test this with your provided data to confirm it works:

• Item A: There are rows where Individual is Fail → COUNTIFS returns 2 → formula outputs blank. Correct.
• Item B: No Individual Fails, but Overall is Fail → formula outputs "issue". Correct.
• Item C: Overall is Pass → formula outputs blank. Correct.

Notes #

• Make sure the ranges (A:A, C:C) cover all your data rows. If your data doesn't start at row 1, adjust the ranges to exclude headers (e.g., A2:A100 instead of A:A).
• Excel is case-insensitive by default, so "fail" vs "Fail" won't matter. If you need case-sensitive checks, replace the text comparisons with EXACT() (e.g., EXACT(B2,"Fail")).

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