Three formulas that all add up numbers conditionally — but they work differently. Here is exactly when to use each one.
SUMIF adds values in a range that match a single condition. It's the right choice when you have one criterion and your data is straightforward.
=SUMIF(range, criteria, [sum_range])
=SUMIF(A2:A100, "North", B2:B100)
-- Sum column B where column A = "North"The limitation: only one condition. The moment you need "North AND Q1" or "North OR South," SUMIF can't handle it.
SUMIFS is the natural upgrade from SUMIF. It handles multiple conditions and is the right default for most conditional summing tasks.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])
=SUMIFS(B2:B100, A2:A100, "North", C2:C100, "Q1")
-- Sum B where A = "North" AND C = "Q1"Note the argument order: SUMIFS puts sum_range first, unlike SUMIF which puts it last. This trips up almost everyone switching between the two.
SUMIFS works perfectly with one condition too — it's strictly more capable than SUMIF. If you only ever learned SUMIFS, you'd never need SUMIF at all.
SUMPRODUCT multiplies arrays together and sums the results. It can do everything SUMIF and SUMIFS do, and significantly more — but the syntax is less readable for simple cases.
=SUMPRODUCT((A2:A100="North") * (C2:C100="Q1") * B2:B100)
-- Same result as the SUMIFS example aboveSUMPRODUCT genuinely shines when you need:
=SUMPRODUCT((A2:A100="North") + (A2:A100="South"), B2:B100)
-- OR condition: sum B where A is North OR South
-- SUMIFS cannot do this directly| Feature | SUMIF | SUMIFS | SUMPRODUCT |
|---|---|---|---|
| One condition | ✓ | ✓ | ✓ |
| Multiple AND conditions | ✗ | ✓ | ✓ |
| OR conditions | ✗ | ✗ | ✓ |
| Wildcard support (* ?) | ✓ | ✓ | ✗ |
| Calculated criteria (MONTH, LEN etc) | ✗ | ✗ | ✓ |
| Readable syntax | ✓ | ✓ | Harder |
| Speed on large datasets | Fast | Fast | Slower |
Use SUMIFS by default. It handles one or multiple AND conditions, has readable syntax, and is fast on large datasets. Use SUMPRODUCT only when you genuinely need OR logic, calculated criteria like MONTH() or LEN(), or array-based conditions that SUMIFS can't express. Avoid SUMIF entirely — SUMIFS does everything it does and more.
ExcelPro has hands-on exercises for every formula on this page — type it yourself, get instant feedback.
Start practising free →