Function comparison · engine-measured

SUMIF vs SUMIFS

SUMIF sums cells that meet one condition; SUMIFS sums cells that meet many (up to 127, all AND-ed). They share the same criteria syntax but disagree on one thing that trips everyone up: where the sum_range goes. Below, every difference is measured on identical data in a live engine, not copied from a feature list. Edit the cells and watch both formulas re-evaluate side by side.

fx
SUMIF(…)  vs  SUMIFS(…)
7
differences measured live
127
max conditions in SUMIFS
1
max condition in SUMIF
2007+
Excel needed for SUMIFS
▶ Try both live See the feature matrix
🏆Verdict: SUMIFS for two or more conditions; SUMIF for a quick single sum

SUMIFS is the more general tool: anything SUMIF does with one condition, SUMIFS does too, plus up to 127 AND-ed conditions. Reach for SUMIF only for muscle memory, the optional-sum_range shortcut (summing the criteria range itself), or pre-2007 compatibility. The one trap to memorize: SUMIF puts sum_range LAST and optional; SUMIFS puts it FIRST and required.

SUMIF strengths

  • Shorter to type for a single condition
  • sum_range is optional — omit it to sum the criteria range itself
  • Runs in every Excel version, including 2003 and earlier
  • An array constant {"East","West"} inside SUM gives OR logic in one formula

SUMIFS strengths

  • Up to 127 criteria pairs, all combined with AND
  • sum_range-first order scales cleanly as you add conditions
  • Identical criteria syntax to SUMIF (numbers, text, ">100", wildcards, cell refs)
  • The modern default for conditional sums in Excel 2007+ and Google Sheets

Feature-by-feature matrix

Twelve capabilities, scored honestly. ✓ full support, ◐ partial / with caveats, ✗ not supported. The split is simple: SUMIF stops at one condition; SUMIFS scales to 127 — but they disagree on where sum_range goes.

CapabilitySUMIFSUMIFSWhy it matters
Number of conditions◐ One✓ Up to 127The headline difference. SUMIFS ANDs every criteria pair.
Position of sum_range◐ Last✓ FirstThe #1 source of errors when switching between them.
sum_range is optional✓ Yes (defaults to range)✗ No (required)Omit it in SUMIF to sum the criteria range itself.
Combine conditions with AND✗ No✓ YesSUMIF has no second-criteria argument.
OR across criteria values◐ Array constant + SUM✗ AND onlySUM(SUMIF(r,{"a","b"},s)) does OR; SUMIFS needs separate calls added.
Wildcard match (* ?)✓ Yes✓ YesBoth support * (any run) and ? (one char) in text criteria.
Comparison operators (> < <> =)✓ Yes✓ YesQuote them: ">100", "<>East". Same syntax in both.
Criteria from a cell reference✓ Yes✓ YesUse F2 or ">"&F2 for an operator plus a cell value.
criteria_range and sum_range differ in size◐ Resizes to range✗ Must matchSUMIF reshapes sum_range from its top-left cell; SUMIFS requires equal dimensions.
Excel 2003 and earlier✓ Yes✗ No (#NAME?)SUMIFS arrived in Excel 2007.
Google Sheets✓ Yes✓ YesBoth supported with identical arguments.
Counting / averaging siblings✓ COUNTIF / AVERAGEIF✓ COUNTIFS / AVERAGEIFSThe same one-vs-many split applies to counting and averaging.

Syntax, side by side

The trap is the argument order. SUMIF puts the optional sum_range last; SUMIFS puts the required sum_range first, then pairs of criteria_range + criteria.

SUMIF

SUMIF(range, criteria, [sum_range])
rangeThe cells to test against the criteria (the criteria_range.
criteriaWhat to match: a number, text, ">100", a wildcard, or a cell reference.
sum_rangeOptional. The cells to add up. Omit it and SUMIF sums range itself.
=SUMIF(A2:A8, "East", D2:D8)

SUMIFS

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
sum_rangeRequired, and FIRST. The cells to add up.
criteria_range1The first range to test.
criteria1The condition applied to criteria_range1.
criteria_range2, criteria2Optional pairs, up to 127 total. Every pair is AND-ed together.
=SUMIFS(D2:D8, A2:A8, "East", B2:B8, "Widget")

Live head-to-head playground

One editable sales table, two formulas. Pick a scenario or type your own — both sides re-evaluate in the same engine, so you see exactly where they diverge.

A1Sample data — click any cell to edit; both formulas re-evaluate live

SUMIF

—

SUMIFS

—
HyperFormula 3.5 · live

Every difference, measured

These are not feature-list claims. Each pair below was evaluated on the identical dataset in the HyperFormula 3.5 engine, and the outputs are baked from that run. Reproduce them in the playground above.

One condition — the baseline tie

Sum Amount where Region is East. Both return the same total; only the argument order differs.
SUMIF
=SUMIF(A2:A8,"East",D2:D8)
3200
SUMIFS
=SUMIFS(D2:D8,A2:A8,"East")
3200
Identical result (3200). SUMIF ends with the sum_range; SUMIFS starts with it. This order swap is the whole learning curve.

Two conditions — SUMIF can't, SUMIFS can

Sum Amount where Region is East AND Product is Widget. SUMIF has no second-criteria argument, so it can only filter by Region.
SUMIF
=SUMIF(A2:A8,"East",D2:D8)
3200
SUMIFS
=SUMIFS(D2:D8,A2:A8,"East",B2:B8,"Widget")
1700
SUMIF returns 3200 (all East, ignoring product); SUMIFS returns 1700 (East AND Widget). For two conditions you need SUMIFS or SUMPRODUCT.

Numeric operator criteria

Sum Amount where Qty is greater than 5. The quoted operator ">5" works identically in both.
SUMIF
=SUMIF(C2:C8,">5",D2:D8)
3500
SUMIFS
=SUMIFS(D2:D8,C2:C8,">5")
3500
Both return 3500. Operators (>, <, >=, <=, <>, =) are quoted strings in both functions.

Wildcard text criteria

Sum Amount where Product starts with W (W*). Both support * and ? wildcards.
SUMIF
=SUMIF(B2:B8,"W*",D2:D8)
2800
SUMIFS
=SUMIFS(D2:D8,B2:B8,"W*")
2800
Both return 2800 (all Widget rows). Wildcards apply to text criteria in both functions.

Omitting sum_range

Sum the Qty values themselves where Qty > 5. SUMIF lets you drop sum_range; SUMIFS requires it.
SUMIF
=SUMIF(C2:C8,">5")
31
SUMIFS
=SUMIFS(C2:C8,C2:C8,">5")
31
Both return 31. SUMIF defaults sum_range to the criteria range; SUMIFS makes you repeat the range as the first argument.

OR logic across criteria values

Sum Amount where Region is East OR West. SUMIF takes an array constant inside SUM; SUMIFS is AND-only.
SUMIF
=SUM(SUMIF(A2:A8,{"East","West"},D2:D8))
4150
SUMIFS
=SUMIFS(D2:D8,A2:A8,"East")+SUMIFS(D2:D8,A2:A8,"West")
4150
Both return 4150. SUM(SUMIF(r,{"East","West"},s)) is the compact OR trick; SUMIFS needs two calls added together.

Not-equal operator

Sum Amount where Region is NOT East (<>East). Another shared operator case.
SUMIF
=SUMIF(A2:A8,"<>East",D2:D8)
1550
SUMIFS
=SUMIFS(D2:D8,A2:A8,"<>East")
1550
Both return 1550 (West + North). The <> operator behaves the same in both.

Engine benchmark

500 conditional sums against tables of increasing size, timed in this page's engine.

Table sizeSUMIFSUMIFS
1,000 rows × 500 lookups474.95 ms454.92 ms
5,000 rows × 500 lookups4856.27 ms4831.27 ms
10,000 rows × 500 lookups17298.33 ms17023.06 ms
Measured in the HyperFormula 3.5 engine on this page, not in Microsoft Excel. Absolute times are engine-specific; use only as a relative signal. Real Excel performance depends on version, data layout and calculation chain.

Which one should you use?

A decision rule for the situations that actually come up. Short version: SUMIFS once you need a second condition; SUMIF for a quick single-condition sum or the omit-sum_range shortcut.

SituationUseWhy
Summing by a single conditionEitherBoth work. SUMIF is a touch shorter; SUMIFS keeps you in one consistent habit.
Two or more conditions (region AND product)SUMIFSSUMIF cannot take a second criteria argument. SUMIFS is built for this.
Summing the criteria range itself (e.g. all Qty > 5)SUMIFSUMIF lets you omit sum_range. SUMIFS forces you to repeat the range as sum_range.
OR logic (East OR West)SUMIFSUM(SUMIF(r,{"East","West"},s)) in one formula. SUMIFS is AND-only — add separate SUMIFS instead.
The file must open in Excel 2003 or earlierSUMIFSUMIFS returns #NAME? before Excel 2007.
A new workbook, team standard, or you expect more conditions laterSUMIFSSUMIFS scales to 127 conditions and its sum_range-first order never changes.
Criteria driven by cell valuesEitherBoth accept F2 or ">"&F2. Identical criteria syntax.

SUMIF → SUMIFS migration

Five translations. The one rule that fixes most errors: move sum_range from the last argument to the first, then add criteria pairs.

TaskSUMIF / old waySUMIFSWhat changed
Single condition=SUMIF(A2:A8, "East", D2:D8)=SUMIFS(D2:D8, A2:A8, "East")Move sum_range from last to first; the criteria pair stays the same.
Add a second condition=SUMIF(A2:A8, "East", D2:D8) (can't add product)=SUMIFS(D2:D8, A2:A8, "East", B2:B8, "Widget")SUMIF has no second criteria; SUMIFS appends another range+criteria pair.
Sum the criteria range itself=SUMIF(C2:C8, ">5")=SUMIFS(C2:C8, C2:C8, ">5")SUMIFS needs an explicit sum_range, so repeat the range.
Numeric operator criteria=SUMIF(C2:C8, ">5", D2:D8)=SUMIFS(D2:D8, C2:C8, ">5")Same quoted operator; only the argument order changes.
Wildcard criteria=SUMIF(B2:B8, "W*", D2:D8)=SUMIFS(D2:D8, B2:B8, "W*")Wildcards behave identically in both.

Frequently asked questions

What is the difference between SUMIF and SUMIFS?
SUMIF sums cells that meet a single condition; SUMIFS sums cells that meet multiple conditions (up to 127 criteria pairs, all combined with AND). They use the same criteria syntax. The practical trap is argument order: SUMIF is SUMIF(range, criteria, [sum_range]) with sum_range last and optional, while SUMIFS is SUMIFS(sum_range, criteria_range1, criteria1, …) with sum_range first and required.
Why is the argument order different?
SUMIF is the older function and made sum_range an optional trailing argument. When Microsoft added multi-condition support in Excel 2007, it put sum_range first so that any number of criteria_range/criteria pairs could follow consistently. Memorize it as: SUMIF ends with the sum range, SUMIFS starts with it.
Can SUMIF handle two conditions?
Not directly — it accepts only one criteria argument. For region AND product you need SUMIFS, or the older SUMPRODUCT approach: SUMPRODUCT((A2:A8="East")*(B2:B8="Widget")*D2:D8). SUMIFS is cleaner and faster to read.
Is SUMIFS faster than SUMIF?
For a single condition they are essentially the same work. In our HyperFormula 3.5 benchmark over 1,000–10,000 rows the two were within a few percent, with no consistent winner. Choose SUMIFS for its multi-condition capability, not for a promised speedup.
Does SUMIFS work in Google Sheets?
Yes. Current Google Sheets supports SUMIFS with the same sum_range-first argument order. It also supports SUMIF. Both are portable across Excel 2007+, Google Sheets and LibreOffice Calc.
How do I do OR logic with SUMIFS?
SUMIFS only ANDs its criteria, so it cannot express East OR West in one call. Either add two SUMIFS — SUMIFS(s,r,"East")+SUMIFS(s,r,"West") — or use the SUMIF array trick SUM(SUMIF(r,{"East","West"},s)), or SUMPRODUCT. The array-constant trick is the most compact.
Which Excel version introduced SUMIFS?
Excel 2007. SUMIF has existed since well before that and runs in every version. If a workbook must open in Excel 2003 or earlier, use SUMIF (or SUMPRODUCT for multiple conditions); SUMIFS will show #NAME? there.