SUMIF and SUMIFS look almost identical, and that's exactly why people mix them up. The difference is small but trips up almost everyone once: the position of the sum range changes.
Here's the clean rule, with examples you can paste straight into Excel.
The one difference that matters
-
SUMIF(range, criteria, [sum_range])— sum range is last (and optional). -
SUMIFS(sum_range, criteria_range1, criteria1, ...)— sum range is first (and required).
That's it. One condition → SUMIF. Two or more → SUMIFS. But because the sum range moves, copying a SUMIF into a SUMIFS without rearranging arguments silently sums the wrong column.
SUMIF: one condition
Sum sales where the region is "East":
=SUMIF(A2:A100, "East", B2:B100)
-
A2:A100= region column (checked against criteria) -
"East"= the condition -
B2:B100= sales column (actually summed)
If you omit the sum range, SUMIF sums the criteria range itself — handy for "sum all values greater than 100":
=SUMIF(B2:B100, ">100")
SUMIFS: two or more conditions
Sum sales where region is "East" and product is "Widget":
=SUMIFS(B2:B100, A2:A100, "East", C2:C100, "Widget")
Notice the sum range B2:B100 jumped to the front. Each condition is a pair: criteria_range, then criteria. All conditions must be true (AND logic).
The mistake I see most often
Someone writes:
=SUMIFS(A2:A100, "East", B2:B100) ← wrong
This treats A2:A100 as the sum range and "East" as a criteria_range — it errors or sums nonsense. The fix is mechanical: move the column you want to total to the first argument.
Quick decision table
| Need | Use | Pattern |
|---|---|---|
| Sum by 1 condition | SUMIF | SUMIF(check_range, cond, sum_range) |
| Sum by 2+ conditions | SUMIFS | SUMIFS(sum_range, range1, cond1, range2, cond2) |
| Sum values > threshold | SUMIF | SUMIF(range, ">100") |
| OR logic (East OR West) | SUMIFS twice | SUMIFS(...,"East")+SUMIFS(...,"West") |
That last row matters: SUMIFS is AND-only. For OR, add two SUMIFS together (or use SUMPRODUCT).
Practice it live
Reading the argument order once isn't enough — you have to type it a few times before it sticks. I built a free browser lab where you edit the formula and see the result update instantly, no signup:
And if you want a printable set of exercises (with answer key) covering lookups, text, dates and statistics, there's a free workbook here: LookupLab practice exercises.
TL;DR
One condition → SUMIF, sum range last. Two-plus → SUMIFS, sum range first. When a sum "looks wrong," check the argument order before you check the data — it's almost always the sum range in the wrong slot.
Top comments (0)