DEV Community

longtermswiv
longtermswiv

Posted on

SUMIF vs SUMIFS: when to use each (with real examples)

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)
Enter fullscreen mode Exit fullscreen mode
  • 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")
Enter fullscreen mode Exit fullscreen mode

SUMIFS: two or more conditions

Sum sales where region is "East" and product is "Widget":

=SUMIFS(B2:B100, A2:A100, "East", C2:C100, "Widget")
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)