SUMIF
Adds up only the cells that meet one condition: the single-criterion sibling of SUMIFS.
SUMIF is exactly what SUMIFS becomes when there's only one condition to check: total expenses for one category, total revenue for one region, total invoice amounts over a threshold. It's simpler to write than SUMIFS for the one-condition case, but that simplicity comes with an argument order that trips people up the moment a second condition shows up.
Reach for SUMIF when a formula will only ever need one test. The moment a second condition is even plausible, SUMIFS is the better starting point, since SUMIFS reads cleanly with any number of criteria from one upward and saves a rewrite later.
Syntax
| Argument | Description |
|---|---|
| range | Required. The cells to test against criteria. |
| criteria | Required. What a cell in range must match: a number, text, or a comparison like ">100". |
| sum_range | Optional. The cells to actually add up, if different from range. Omit it and SUMIF adds up range itself. |
The mistake almost everyone makes
Forgetting sum_range when the column you want to total isn't the same column you're testing. Leave sum_range out and SUMIF doesn't error; it just adds up range itself, which silently gives you a number from the wrong column entirely. If the column you're testing happens to also contain numbers, the formula will return a result that looks entirely plausible while answering a completely different question than the one you meant to ask.
The related mistake is giving criteria a range and sum_range of different sizes. They need to line up row for row, exactly like every other member of the SUMIF/SUMIFS/COUNTIFS/AVERAGEIFS family.
A real example: totaling expense submissions by category
Say you're reviewing an expense report and need the total for just the Travel category:
Column A holds the category, column B the amount, and sum_range (column B) is explicitly given rather than left to default. This is the habit worth building even when range and sum_range happen to be the same column today: being explicit means the formula still works correctly if someone later inserts a new amount column next to the category column.
Worked examples
| A | B | |
|---|---|---|
| 1 | Category | Amount |
| 2 | Travel | 120 |
| 3 | Meals | 45 |
| 4 | Travel | 80 |
| A | B | |
|---|---|---|
| 1 | Invoice | Total |
| 2 | INV-1 | 90 |
| 3 | INV-2 | 210 |
| 4 | INV-3 | 150 |
No separate sum_range needed when the test range and the numbers to add are the same column.
Common mistakes
- Forgetting sum_range when the column to total isn't the column being tested; without it, SUMIF just adds up range itself.
- Giving criteria range and sum_range different sizes. They need to line up row for row.
- Reaching for SUMIF when a second condition is about to be needed. Starting with SUMIFS from the outset avoids a rewrite later.
Related functions
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice SUMIF →