Math & Trig
Dashboard SUMIFS with an "All" dropdown option
Switch cleanly between total revenue when a filter cell equals "All" and filtered SUMIFS when a specific category is chosen.
Why IF(Filter="All", SUM(...), SUMIFS(...)) beats the "*" wildcard shortcut
A popular trick writes SUMIFS(B2:B5, A2:A5, IF(C2="All", "*", C2)). However, the "*" wildcard matches only non-empty text cells—silently dropping rows whose category cell is blank or numeric!
Branching explicitly with IF(C2="All", SUM(B2:B5), SUMIFS(B2:B5, A2:A5, C2)) guarantees the "All" view matches the true grand total even if some rows are uncategorized.
The example changes C2 directly; a dropdown UI must be configured separately. Reserve All as a control value distinct from real category names. Literal category names containing wildcard characters need escaping in SUMIFS.
| Channel (A) | Revenue (B) | Filter = "All" | Filter = "Direct" |
|---|---|---|---|
| Direct | 4200 | Included | Included |
| Partner | 3100 | Included | Excluded |
| Direct | 1800 | Included | Included |
| Self-Serve | 2900 | Included | Excluded |
Explicit All-or-filtered total
When SelectedFilter C2 = "All", SUM returns 4,200 + 3,100 + 1,800 + 2,900 = 12,000. Switching C2 to "Direct" returns 4,200 + 1,800 = 6,000.
=IF(C2="All", SUM(B2:B5), SUMIFS(B2:B5, A2:A5, C2))Text-only wildcard alternative
When every row in A2:A5 is guaranteed to hold non-empty text, IF(C2="All", "*", C2) inside SUMIFS produces the same 12,000.
=SUMIFS(B2:B5, A2:A5, IF(C2="All", "*", C2))Try the example
Use the original values or change them to test your understanding.