SUMIF in Excel: How to Use It with Examples
SUMIF adds up only the numbers that meet a condition you set. Instead of adding a whole column and then subtracting what you do not want, you tell Excel the rule once — “only this product”, “only sales above this amount” — and it totals just those rows. It is the first conditional formula most people need, and it answers a huge number of everyday spreadsheet questions.
The SUMIF syntax
=SUMIF(range, criteria, [sum_range])
- range — the cells to test against your condition.
- criteria — the condition itself, in quotes for text and comparisons (for example “Apples” or “>500”). A cell reference works too, without quotes.
- [sum_range] — optional: the cells to actually add up. Leave it out only when the range you are testing is also the range you want to sum.
A worked example
Suppose a small sales list has products in A2:A6 and amounts in B2:B6:
| Product (A) | Amount (B) |
|---|---|
| Apples | 250 |
| Bananas | 400 |
| Apples | 175 |
| Oranges | 520 |
| Apples | 300 |
To total just the Apples sales, use:
=SUMIF(A2:A6, “Apples”, B2:B6)
Excel checks each row in column A, keeps only the Apples rows (250, 175, 300), and returns 725. The other rows are ignored, not subtracted.
Criteria with numbers and comparisons
Criteria are not limited to exact text. To sum only amounts above 300 in the same table, test the amount column itself and leave out sum_range, since the tested range is the summed range:
=SUMIF(B2:B6, “>300”)
That returns 920 (400 + 520). Other useful forms: “<=100” for amounts up to 100, “<>0” to exclude zeroes, and “Ap*” with a wildcard to match any text starting with “Ap”. To use a value from a cell instead of typing it — say the product name is in D2 — write =SUMIF(A2:A6, D2, B2:B6) with no quotes around the reference.
SUMIF vs SUMIFS
SUMIF handles one condition. When you need two or more — “Apples AND in March”, for example — use SUMIFS, which takes the sum_range first and then pairs of range/criteria: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2). Learn SUMIF first; SUMIFS is the same idea with more conditions, and most real reports need it sooner than you expect.
Common mistakes
- Forgetting the quotes around text criteria or comparisons typed directly in the formula (“Apples” and “>300” need quotes; a cell reference like D2 does not).
- Ranges of different sizes — range and sum_range should cover the same rows, or Excel pairs the wrong rows together.
- Numbers stored as text — if a cell looks like a number but is stored as text, SUMIF skips it silently. Convert the column to numbers first if a total looks too low.
- Extra spaces — “Apples ” with a trailing space does not match “Apples”. Trim the data or use a wildcard if the source data is messy.
Quick recap
=SUMIF(where to check, the condition, what to add). Text, comparisons, wildcards, and cell references all work as criteria, and SUMIFS extends the same pattern to multiple conditions. If you ever find yourself adding a column by hand and deleting rows that do not count, SUMIF is the formula that replaces that chore.
