The SUMIF function in Excel
SUMIF adds only the numbers whose row matches a condition. Total sales of the North region, total of all orders above 100, all expenses in a category.
Note the argument order: the condition comes before the range to add. In SUMIFS, the range to add comes first.
Syntax
=SUMIF(range, criteria, [sum_range])
| Argument | What it means |
|---|---|
| range | The cells to check against the condition. |
| criteria | The condition: "North", ">100", a cell reference, or a wildcard pattern. |
| sum_rangeoptional | The cells to add. If left out, the cells in range are added. |
Examples
1. Total for one category
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | North total | |
| 2 | 1001 | North | 250 | Paid | 745 | |
| 3 | 1002 | South | 90 | Paid | ||
| 4 | 1003 | North | 420 | Open | ||
| 5 | 1004 | East | 130 | Paid | ||
| 6 | 1005 | North | 75 | Paid |
Check the regions in B, add the amounts in C where the region is North: 250 + 420 + 75 = 745.
2. Add only large amounts
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | Sum over 100 | |
| 2 | 1001 | North | 250 | Paid | 800 | |
| 3 | 1002 | South | 90 | Paid | ||
| 4 | 1003 | North | 420 | Open | ||
| 5 | 1004 | East | 130 | Paid | ||
| 6 | 1005 | North | 75 | Paid |
When the condition is about the numbers themselves, leave out sum_range: 250 + 420 + 130 = 800.
3. Condition from a cell
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | Region | South | |
| 2 | 1001 | North | 250 | Paid | Total | 90 | |
| 3 | 1002 | South | 90 | Paid | |||
| 4 | 1003 | North | 420 | Open | |||
| 5 | 1004 | East | 130 | Paid | |||
| 6 | 1005 | North | 75 | Paid |
Type a region in G1 and the total follows. South has one order of 90.
Common mistakes
Mixing up the order with SUMIFS.
=SUMIF(C2:C6,B2:B6,"North")doesn't work.Fix: SUMIF: condition range, condition, sum range. SUMIFS: sum range first, then the pairs.
Ranges of different sizes. SUMIF quietly resizes sum_range, which can add the wrong rows.
Fix: Make range and sum_range cover exactly the same rows.
Frequently asked questions
How do I sum with two conditions?
Use SUMIFS, which takes as many conditions as you need.
Can SUMIF add values that are NOT equal to something?
Yes, with "<>": =SUMIF(B2:B6,"<>North",C2:C6) adds everything outside the North.
Related functions
- SUMIFSSUMIFS adds up values that meet several conditions at once, such as paid orders from one region.
- COUNTIFCOUNTIF counts the cells in a range that meet one condition: a text, a number, a comparison like ">100" or a wildcard.
- IFIF checks a condition and returns one value if it's true and another if it's false.