Conditional Aggregation
Cell Equals, Count, or Sum One of Many Values (Allowlist COUNTIF + SUMPRODUCT)
Replace brittle 10-way OR chains with a range-driven COUNTIF allowlist flag to test, count, and sum rows matching any value in an editable lookup list.
Why hardcoding OR(B2="North", B2="West", B2="LATAM") breaks the moment regions change!
Hardcoding long OR(...) strings inside IF, COUNTIF, or SUMIF formulas buries business rules inside formula text and forces manual formula rewrites whenever finance adds or removes a territory! Storing allowed territories in a visible table range F2:F4 and testing --(COUNTIF($F$2:$F$4, B2) > 0) turns multi-value membership into an instant, table-driven 1/0 allowlist flag!
Once helper column D2:D7 flags whether each transaction’s region B2 appears in allowlist $F$2:$F$4, SUM(D2:D7) counts all matching rows (4 orders across North, West, and LATAM) and SUMPRODUCT(D2:D7, C2:C7) sums their exact revenue ($5,200) without double-counting!
| Allowlist Range F2:F4 | Matching Orders SUM(D2:D7) | Allowlist Revenue SUMPRODUCT(D2:D7, C2:C7) | Operational Benefit |
|---|---|---|---|
| North, West, LATAM | 4 orders | $5,200 | Zero hardcoded text inside formulas |
| North, West, East (swap LATAM -> East) | 4 orders | $5,550 | Updates instantly from cell F4 |
| APAC, MEA, ANZ (0 matches) | 0 orders | $0 | Clean zero without #N/A errors |
Sum Amounts Where Category Equals Any Value in Allowlist
Multiplies the per-row allowlist membership flag D2:D7 = --(COUNTIF($F$2:$F$4, B2) > 0) by transaction revenue C2:C7.
=SUMPRODUCT(D2:D7, C2:C7)Count Rows Matching Any Value in Allowlist
Totals the 1/0 allowlist indicator column D2:D7 across all transactions.
=SUM(D2:D7)Try the example
Use the original values or change them to test your understanding.