SUMIF and SUMIFS add up numbers that meet conditions. SUMIF handles a single condition; SUMIFS handles multiple. They're among the most useful functions for any kind of reporting or analysis.
SUMIF: one condition
=SUMIF(range, criteria, [sum_range])
- range — the cells to test against your criteria.
- criteria — the condition (a value, text, or expression).
- sum_range — the cells to actually add up (if different from range).
Example — sum all sales (column B) for the "East" region (column A):
=SUMIF(A2:A100, "East", B2:B100)
This checks column A for "East" and sums the matching values in column B.
Criteria you can use
Criteria are flexible:
- Exact text:
"East" - Numbers:
100 - Comparisons:
">100","<=50","<>0"(note the quotes around operators) - Wildcards:
"East*"matches anything starting with East;"*North*"matches anything containing North. - Cell references:
">"&C1uses the value in C1 as the threshold.
SUMIFS: multiple conditions
SUMIFS handles several conditions at once, and note the argument order is different — the sum range comes first:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
Example — sum sales for "East" region AND amounts over 100:
=SUMIFS(B2:B100, A2:A100, "East", B2:B100, ">100")
You can keep adding criteria range/criteria pairs. All conditions must be true for a row to be included (it's AND logic).
The argument-order trap
This trips people up constantly:
- SUMIF: range, criteria, sum_range (sum range is last)
- SUMIFS: sum_range, criteria_range, criteria (sum range is first)
If your SUMIFS returns 0 or an error unexpectedly, check that you put the sum range first. It's the single most common SUMIFS mistake.
Related functions
The same pattern applies across Excel: COUNTIF/COUNTIFS count matching cells, and AVERAGEIF/AVERAGEIFS average them. Once you understand the criteria logic here, those work identically.
Tip: prefer SUMIFS even for one condition
Because SUMIFS has a consistent argument order and scales to more conditions later, many people use SUMIFS even for single-condition sums, just to avoid the argument-order confusion between the two functions. It's a reasonable habit.