If status is Open AND amount is over 1000, return High, else if either flag is Urgent OR amount > 5000 return Critical, otherwise Low

公式

=IF(OR(C2="Urgent", B2>5000), "Critical", IF(AND(C2="Open", B2>1000), "High", "Low"))

说明

Excel evaluates IF from the outside in. Check the highest-priority rule first (Critical) so a row cannot be labeled High when it should be Critical.

步骤

  1. OR tests the Critical rule first.
  2. If that fails, AND tests the High rule.
  3. Anything else falls through to Low.

变体

IFS (Excel 2019+)

Flatter than nested IF. TRUE is the default branch.

=IFS(OR(C2="Urgent", B2>5000), "Critical", AND(C2="Open", B2>1000), "High", TRUE, "Low")

Include a blank-safe check

Leaves the cell empty until an amount is entered.

=IF(B2="", "", IF(OR(C2="Urgent", B2>5000), "Critical", "Normal"))

Numeric score instead of labels

Useful when you later SUM the priority scores.

=IF(AND(C2="Open", B2>1000), 2, IF(C2="Urgent", 3, 1))