Conditional Aggregation
SUMIFS with Multiple Criteria and OR Logic Without Double-Counting Rows
Distinguish safe same-column OR addition in SUMIFS from overlapping cross-column OR conditions where naive SUMIF + SUMIF double-counts rows that satisfy both rules.
Predict the total by hand before writing an OR formula
Enter five freight orders in A2:D6: ORD-101 (West, Standard, 400), ORD-102 (West, Rush, 650), ORD-103 (East, Rush, 500), ORD-104 (North, Standard, 300), and ORD-105 (South, Standard, 250). First, calculate by hand the Standard orders in West OR North: 400 + 300 = 700. Because a single row in column B cannot simultaneously equal "West" and "North", adding two SUMIFS calls on the SAME column is mutually exclusive and exact.
Now predict the total of orders that are EITHER in "West" (column B) OR marked "Rush" (column C): rows ORD-101 (400), ORD-102 (650), and ORD-103 (500) qualify once each, totaling 1,550. Try the tempting shortcut =SUMIF(B2:B6, "West", D2:D6) + SUMIF(C2:C6, "Rush", D2:D6): it returns 2,200 because ORD-102 is both West AND Rush, so its 650 amount gets counted twice!
| OR Pattern | Formula Structure | Result on Sample Table |
|---|---|---|
| Same-Column OR + AND | SUMIFS(..., B, "West", C, "Standard") + SUMIFS(..., B, "North", C, "Standard") | 700 (Exact — mutually exclusive) |
| Cross-Column Naive Sum (Bug) | SUMIF(B2:B6, "West", D2:D6) + SUMIF(C2:C6, "Rush", D2:D6) | 2,200 (Wrong — double-counts ORD-102) |
| Cross-Column Boolean Union | SUMPRODUCT(--(((B2:B6 = "West") + (C2:C6 = "Rush")) > 0), D2:D6) | 1,550 (Exact — 1/0 per qualifying row) |
Same-Column OR + Second-Column AND Formula
When both OR options test the same column (Region = "West" or "North") alongside Tier = "Standard", sum the two mutually exclusive SUMIFS terms.
=SUMIFS(D2:D6, B2:B6, "West", C2:C6, "Standard") + SUMIFS(D2:D6, B2:B6, "North", C2:C6, "Standard")Cross-Column Deduplicated OR Union Formula & Hint Check
Add the row-level boolean tests (B2:B6 = "West") + (C2:C6 = "Rush"), clamp any 1 or 2 to a single 1 via --(... > 0), and multiply inside SUMPRODUCT. If you practise with the in-app Pro assistant (which uses Pro credits on submission), try asking: “Do not edit any cells yet. Give me one hint explaining why my two-SUMIF total is 650 higher than the matching rows.” Then change D3 from 650 to 800 (+150): the deduplicated union rises by 150 (to 1,700), whereas the naive sum jumps by 300 (to 2,500).
=SUMPRODUCT(--(((B2:B6 = "West") + (C2:C6 = "Rush")) > 0), D2:D6)Try the example
Use the original values or change them to test your understanding.