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.
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.
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.
| Capability | SUMIF | SUMIFS | Why it matters |
|---|---|---|---|
| Number of conditions | ◐ One | ✓ Up to 127 | The headline difference. SUMIFS ANDs every criteria pair. |
| Position of sum_range | ◐ Last | ✓ First | The #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 | ✓ Yes | SUMIF has no second-criteria argument. |
| OR across criteria values | ◐ Array constant + SUM | ✗ AND only | SUM(SUMIF(r,{"a","b"},s)) does OR; SUMIFS needs separate calls added. |
| Wildcard match (* ?) | ✓ Yes | ✓ Yes | Both support * (any run) and ? (one char) in text criteria. |
| Comparison operators (> < <> =) | ✓ Yes | ✓ Yes | Quote them: ">100", "<>East". Same syntax in both. |
| Criteria from a cell reference | ✓ Yes | ✓ Yes | Use F2 or ">"&F2 for an operator plus a cell value. |
| criteria_range and sum_range differ in size | ◐ Resizes to range | ✗ Must match | SUMIF 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 | ✓ Yes | Both supported with identical arguments. |
| Counting / averaging siblings | ✓ COUNTIF / AVERAGEIF | ✓ COUNTIFS / AVERAGEIFS | The same one-vs-many split applies to counting and averaging. |
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.
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.
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.
=SUMIF(A2:A8,"East",D2:D8)=SUMIFS(D2:D8,A2:A8,"East")=SUMIF(A2:A8,"East",D2:D8)=SUMIFS(D2:D8,A2:A8,"East",B2:B8,"Widget")=SUMIF(C2:C8,">5",D2:D8)=SUMIFS(D2:D8,C2:C8,">5")=SUMIF(B2:B8,"W*",D2:D8)=SUMIFS(D2:D8,B2:B8,"W*")=SUMIF(C2:C8,">5")=SUMIFS(C2:C8,C2:C8,">5")=SUM(SUMIF(A2:A8,{"East","West"},D2:D8))=SUMIFS(D2:D8,A2:A8,"East")+SUMIFS(D2:D8,A2:A8,"West")=SUMIF(A2:A8,"<>East",D2:D8)=SUMIFS(D2:D8,A2:A8,"<>East")500 conditional sums against tables of increasing size, timed in this page's engine.
| Table size | SUMIF | SUMIFS |
|---|---|---|
| 1,000 rows × 500 lookups | 474.95 ms | 454.92 ms |
| 5,000 rows × 500 lookups | 4856.27 ms | 4831.27 ms |
| 10,000 rows × 500 lookups | 17298.33 ms | 17023.06 ms |
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.
| Situation | Use | Why |
|---|---|---|
| Summing by a single condition | Either | Both work. SUMIF is a touch shorter; SUMIFS keeps you in one consistent habit. |
| Two or more conditions (region AND product) | SUMIFS | SUMIF cannot take a second criteria argument. SUMIFS is built for this. |
| Summing the criteria range itself (e.g. all Qty > 5) | SUMIF | SUMIF lets you omit sum_range. SUMIFS forces you to repeat the range as sum_range. |
| OR logic (East OR West) | SUMIF | SUM(SUMIF(r,{"East","West"},s)) in one formula. SUMIFS is AND-only — add separate SUMIFS instead. |
| The file must open in Excel 2003 or earlier | SUMIF | SUMIFS returns #NAME? before Excel 2007. |
| A new workbook, team standard, or you expect more conditions later | SUMIFS | SUMIFS scales to 127 conditions and its sum_range-first order never changes. |
| Criteria driven by cell values | Either | Both accept F2 or ">"&F2. Identical criteria syntax. |
Five translations. The one rule that fixes most errors: move sum_range from the last argument to the first, then add criteria pairs.
| Task | SUMIF / old way | SUMIFS | What 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. |