SUMIFS and COUNTIFS look almost identical and answer two completely different questions. One totals the values that match your conditions; the other counts how many rows match. Mixing them up — or reaching for a pivot table when a single formula would do — is the most common stumble in conditional reporting. This lays out exactly when each fits, the one syntax difference that trips everyone up, and the multi-criteria patterns that cover most real questions.
The one difference that matters
Both functions filter rows by one or more conditions. The difference is what they do with the matches: SUMIFS adds up a column of values, COUNTIFS just counts the rows. That shows up as a single change in the syntax — SUMIFS needs a sum range to total, COUNTIFS doesn’t.
=SUMIFS(sum_range, criteria_range1, criteria1, ...)
=COUNTIFS(criteria_range1, criteria1, ...)SUMIFS leads with the range to total; everything after is pairs of “where to look” and “what to match.” COUNTIFS skips the sum range entirely and goes straight to the criteria pairs, because it’s only counting. Get those first arguments straight and the rest of both functions is identical — the same criteria pairs, the same logic. The question to ask before you type either one: do I want a total, or a head count?
That question is worth pausing on, because the two answers lead to genuinely different reports. A total tells you size — revenue, hours, spend. A count tells you frequency — how many orders, how many late deliveries. Plenty of reporting mistakes come from showing one when the reader needed the other: a big total can hide that it came from two huge orders, while a count of two would have flagged it. Pick the function that matches the decision the number is meant to inform.
SUMIFS: total what matches
Use SUMIFS when the answer is an amount — revenue from one region, hours on one project, fuel spend in one month. Set up a small expense sheet to follow along:
| A | B | C | |
|---|---|---|---|
| 1 | Category | Region | Amount |
| 2 | Fuel | North | 120 |
| 3 | Fuel | South | 90 |
=SUMIFS(C2:C, A2:A, "Fuel", B2:B, "North")This totals the Amount column for rows where Category is “Fuel” and Region is “North” — both conditions must hold. The sum range comes first (C2:C), then each criteria pair narrows what gets added. Add more pairs and the conditions stack with AND logic: every condition has to be true for a row to count toward the total.
One practical note on the sum range: it has to be the same height as every criteria range, or the formula errors. C2:C paired with A2:A is fine because both run to the bottom; C2:C100 paired with A2:A is not. When a SUMIFS returns #VALUE for no obvious reason, mismatched range heights are almost always the cause — line them up and it resolves on the spot.
COUNTIFS: count what matches
Use COUNTIFS when the answer is a number of rows — how many fuel purchases, how many deals in negotiation, how many overdue tasks. Same criteria, no sum range:
=COUNTIFS(A2:A, "Fuel", B2:B, "North")This counts the rows where Category is “Fuel” and Region is “North” — two in a full sheet, regardless of the amounts. The criteria pairs work exactly as they do in SUMIFS; you’ve just dropped the sum range because you’re counting rows, not adding values. If you ever find yourself writing =SUMIFS(...)/AVERAGE(...) to get a count, COUNTIFS is the function you actually wanted.
COUNTIFS answers questions a glance can’t. “How many deals are both over $5,000 and still open?” is two criteria on two columns — =COUNTIFS(value_range, ">5000", status_range, "Open") — and the count tells you the size of the problem at once. It turns a wall of rows into a single number you can act on, which is often exactly what a report needs instead of the full list.
Multiple criteria and date ranges
The real power of both functions is stacking conditions, and the most common case is a date range — everything between two dates. Because each criterion is a separate pair, a range is just two conditions on the same column:
=SUMIFS(C2:C, A2:A, "Fuel", D2:D, ">=2026-01-01", D2:D, "<=2026-01-31")This sums fuel spend in January by bounding the date column on both sides. The >= and <= live inside quotes as text, which is how SUMIFS and COUNTIFS express comparisons.
Three or more conditions stack the same way — category, region, and a date range is just four pairs after the sum range. There's no practical limit that matters for a report, and each pair you add narrows the result further. The order of the pairs doesn't change the answer, so write them in whatever order reads clearest to the next person who opens the sheet.
">="&F1. That keeps the formula live — change the date in F1 and the total updates.
Wildcards and the mistakes to avoid
For partial text matches, both functions accept wildcards: * for any characters, ? for a single one. =COUNTIFS(A2:A, "Fuel*") counts every category starting with "Fuel". A handful of errors account for most broken SUMIFS and COUNTIFS formulas:
- ✓ In SUMIFS the sum range comes first; in COUNTIFS there is no sum range — swapping them is the #1 error
- ✓ Every criteria range is the same height as the sum range, or Excel throws #VALUE
- ✓ Operators live inside the quotes:
">=100", not>="100" - ✓ Conditions are AND, not OR — for OR, add two SUMIFS together
That last point catches people often: SUMIFS can't do "Fuel OR Travel" in one set of criteria, because stacked conditions are always AND. The fix is to add two SUMIFS — one per value — which reads cleanly and avoids a tangle of nested logic. For more than a couple of OR conditions, a pivot table is usually the better tool.
Which one, and when a pivot wins
=SUMIFS(C2:C, A2:A, F1, B2:B, F2) with the category in F1 and the region in F2. One formula becomes a mini-report you re-run by changing two cells instead of editing the formula.
Reach for SUMIFS when you want a total and COUNTIFS when you want a count — the choice is that simple once you've named the question. Both shine for a specific figure dropped into a report: "January fuel spend in the North" is one SUMIFS, live and self-updating, where a pivot table would be overkill. When you need many such figures broken down every which way, a pivot table summarizes faster, and for flexible lookups rather than aggregation, XLOOKUP is the right tool. Keep SUMIFS and COUNTIFS for the targeted, conditional totals and counts they do better than anything else — and Microsoft's SUMIFS reference documents every argument when you need it. Learn the two, keep the sum-range rule straight, and most conditional totals and counts become a single line you can trust. Between them they cover an enormous share of everyday reporting, and once the sum-range distinction is second nature you'll reach for them before you reach for a pivot table.