Excel SUMIF Function: Add Up Only the Rows That Match
Adds the numbers in a range, but only on the rows where a condition is met.
SUMIF is SUM with a filter attached. Rather than totalling everything, it looks down one range for rows that match a condition and adds up the corresponding numbers from another range. Total sales for the North region, total spend on one category, total of everything over £1,000 — all one formula.
The argument order surprises people: you give it the range to test first, then the condition, then the range to add. That last argument is optional, and leaving it out means 'add up the same range you tested', which is what you want when the condition is about the numbers themselves.
SUMIF handles exactly one condition. The moment you need two — North *and* Hardware — you want SUMIFS, which is a different function with a different argument order.
Syntax
=SUMIF(range, criteria, [sum_range])Arguments
rangeRequired- The cells to test against the condition.
criteriaRequired- What counts as a match. A value ("North"), a comparison in quotes (">1000"), a cell reference, or a wildcard pattern ("A*").
sum_rangeOptional- The cells to actually add. Omit it and SUMIF adds the cells in range itself.
The example data
Headers in row 1, data in A2:D6.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Rep | Category | Amount |
| 2 | North | Alice | Hardware | 1240 |
| 3 | South | Bruno | Software | 385 |
| 4 | North | Chen | Hardware | 2100 |
| 5 | South | Alice | Software | 940 |
| 6 | North | Bruno | Software | 156 |
Worked examples
=SUMIF(A2:A6, "North", D2:D6)Result: 3496
Tests column A for North, adds the matching rows of column D: 1240 + 2100 + 156.
=SUMIF(D2:D6, ">1000")Result: 3340
No third argument, so it adds the same cells it tested — every amount over 1000.
=SUMIF(C2:C6, "Hardware", D2:D6)Result: 3340
Same total by a different route: the two Hardware rows.
=SUMIF(B2:B6, "A*", D2:D6)Result: 2180
The * wildcard matches any rep whose name starts with A — both of Alice's rows.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
Returns 0 when rows clearly match
Why it happens: The criteria does not match the stored value: a trailing space, or numbers stored as text so ">1000" compares against text.
How to fix it: Clean the data with TRIM, and check the numeric column really is numeric — text numbers sit left-aligned by default.
#VALUE!
Why it happens: range and sum_range are different shapes, or the formula points at a closed workbook.
How to fix it: Make both ranges the same height and width. SUMIF is lenient about this in some versions and wrong rather than erroring, so do not rely on it.
Wrong total, no error
Why it happens: sum_range starts on a different row from range, so the rows do not line up — testing A2:A6 but summing D3:D7.
How to fix it: Both ranges must begin on the same row. Excel silently offsets sum_range to match the size of range, which turns a typo into a plausible wrong number.
Tips worth knowing
- Put the criteria in a cell and reference it — =SUMIF(A2:A6, F1, D2:D6) — so the reader can change what is being totalled without editing a formula.
- Build comparisons against a cell with & : ">"&F1 tests greater than whatever F1 holds.
- Use "<>" to sum everything that is not a value: =SUMIF(C2:C6, "<>Hardware", D2:D6).
- SUMIF ignores text and blanks in the sum range rather than erroring, so a stray label in a numeric column will not break it — but it will not be counted either.
Frequently asked questions
What is the difference between SUMIF and SUMIFS?
SUMIF takes one condition, SUMIFS takes up to 127. They also take their arguments in opposite orders: SUMIF is (range, criteria, sum_range) while SUMIFS is (sum_range, range1, criteria1, ...). Many people just use SUMIFS for everything to avoid the mental switch.
Can SUMIF use two criteria?
Not directly. You can add two SUMIFs together for an OR condition, but for AND you need SUMIFS. If you find yourself nesting SUMIFs, that is the signal to switch.
Why does SUMIF return 0?
Either nothing matches, or something matches but not in the form you think. The usual causes are a trailing space in the data, a number stored as text, or a criteria string with a typo. Test with =COUNTIF(range, criteria) — if that also returns 0, the problem is the match, not the sum.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.