The pattern
=SUMIFS(SumRange, CriteriaRange1, Criteria1, CriteriaRange2, Criteria2)
=COUNTIFS(CriteriaRange1, Criteria1, ...)
=AVERAGEIFS(AvgRange, CriteriaRange1, Criteria1, ...)Argument order differs
SUMIFS takes the sum range first, then the criteria pairs. The older SUMIF takes the criteria range first. Mixing them up is a frequent error and produces a plausible wrong number rather than an error.
Criteria forms
| Criteria | Meaning |
|---|---|
"East" | Exact match |
">1000" | Comparison, in quotes |
">"&B1 | Comparison against a cell |
"Ltd" | Wildcard contains |
"<>"&"" | Not blank |
">="&DATE(2024,1,1) | Date comparison |
Comparisons must be text strings, which is why the ampersand is needed when referencing a cell.
A reporting grid
With regions down the side and months across the top, one formula fills the whole grid:
=SUMIFS(Sales[Revenue],
Sales[Region], $A2,
Sales[Month], B$1)Mixed references — $A2 and B$1 — let it fill right and down. That is the standard hand-built summary, and unlike a pivot it updates without refreshing.
These or a pivot table
| Need | Use |
|---|---|
| Exploring, regrouping quickly | Pivot table |
| A fixed report layout | SUMIFS |
| Formulas feeding other formulas | SUMIFS |
| Large data, many combinations | Pivot table |
Pivots are faster to build and faster to recalculate on large data. SUMIFS gives you an exact layout and live results with no refresh step. Most real workbooks use both.