SUMIF vs SUMIFS: what's the difference?
SUMIFS does everything SUMIF does and handles multiple conditions — but the two put their arguments in OPPOSITE orders, which is the bug most people actually hit when switching between them.
The differences at a glance
| SUMIF | SUMIFS | |
|---|---|---|
| Number of conditions | Exactly one | One or many (all ANDed) |
| Argument order | criteria range, criteria, THEN sum range (last) | sum range FIRST, then range/criteria pairs |
| Sum range optional? | Yes — omit it to sum the criteria range itself | No — always explicit |
| Wildcards and comparisons (">100", "Widg*") | Yes | Yes |
| Compatibility | Universal | Universal (Excel 2007+, all Sheets & LibreOffice versions we test) |
Which should you use?
- SUMIF — One condition and you're summing the same column you're testing — =SUMIF(A:A,">100") is the shortest form.
- SUMIFS — Everything else. Even with one condition, using SUMIFS consistently avoids the argument-order flip when you later add a second condition.
Compatibility (from executed tests)
Both execute correctly in every version of all three apps we test — this choice is purely about syntax and conditions, not compatibility. The same relationship holds for COUNTIF/COUNTIFS and AVERAGEIF/AVERAGEIFS.
Example formulas
| SUMIF (sum range LAST) | =SUMIF(A2:A10,"East",C2:C10) |
| SUMIFS (sum range FIRST, two conditions) | =SUMIFS(C2:C10,A2:A10,"East",B2:B10,"Widget") |
Full per-version details on each function page: SUMIF · SUMIFS.