Logical & Conditional
Mark, Count & Sum Rows Matching Mixed AND + OR Rules (IF + COUNTIF + SUMIF)
Flag audit rows meeting compound AND + OR criteria (e.g., Amount >= $1,000 AND Status is Urgent OR Escalated), then count and sum flagged exposure cleanly.
Why native COUNTIFS struggles with mixed AND + OR logic—and a visible Flag column wins!
Native COUNTIFS and SUMIFS combine every argument with strict AND logic—so testing "Amount >= $1,000 AND (Status = Urgent OR Status = Escalated)" forces awkward doubled SUMIFS formulas that are error-prone to audit! Adding an explicit helper column D2 = IF(AND(C2 >= 1000, OR(B2 = "Urgent", B2 = "Escalated")), "Flag", "") makes row-by-row qualification transparent to reviewers and lets downstream COUNTIF and SUMIF summarize flagged rows effortlessly!
Across five accounts-payable invoices totaling $7,250, only three meet BOTH the >= $1,000 materiality threshold AND Urgent/Escalated priority ($1,400 + $1,850 + $1,150 = $4,400 across 3 flagged rows)—skipping $2,200 Routine and $650 small Urgent items automatically!
| Ticket Priority (B) | Amount (C) | Helper Flag D = IF(AND(C>=1000, OR(...))) | Audit Action |
|---|---|---|---|
| Urgent | $1,400 | >=$1,000 & Urgent | "Flag" | Included ($4,400 baseline sum) |
| Routine | $2,200 | Fails Priority OR | "" (Blank) | Excluded despite high amount |
| Urgent | $650 (raised to $1,200) | Crosses $1,000 gate | "" -> "Flag" | Flagged sum rises to $5,600 |
Sum Total Dollar Exposure Across Flagged Mixed-Logic Rows
Sums invoice amounts C2:C6 wherever helper column D2:D6 = IF(AND(C2 >= 1000, OR(B2 = "Urgent", B2 = "Escalated")), "Flag", "") evaluates to "Flag".
=SUMIF(D2:D6, "Flag", C2:C6)Count Rows Passing Compound AND + OR Screening
Counts rows marked "Flag" in D2:D6.
=COUNTIF(D2:D6, "Flag")Try the example
Use the original values or change them to test your understanding.