Excel practice › Excel functions › SUMIF
SUMIF in Excel: add up the rows that meet one condition
SUMIF adds up the numbers in a range that meet one condition. With categories in A2:A8 and amounts in C2:C8, the total spent on travel is =SUMIF(A2:A8,"Travel",C2:C8).
The condition can be a text, a number, a comparison like ">200" or a cell. For two or more conditions at once, use SUMIFS.
Syntax
=SUMIF(range, criteria, [sum_range])
range- The cells to test against the condition, for example the Category column.
criteria- The condition: a number (
60), a text in quotes ("Travel"), a comparison in quotes (">200") or a cell (E2). Text, and anything with a comparison symbol, has to be in double quotes. sum_range- The cells to add. Optional: leave it out and SUMIF adds the cells of
rangeitself. It should be the same size and shape asrange.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Category | Item | Amount | Category | Total | |
| 2 | Travel | Flight to Denver | 412 | Travel | 846 | |
| 3 | Software | Design suite | 60 | |||
| 4 | Meals | Team lunch | 86 | |||
| 5 | Travel | Rental car | 145 | |||
| 6 | Software | Video call plan | 150 | |||
| 7 | Meals | Client dinner | 214 | |||
| 8 | Travel | Flight to Austin | 289 |
E2 with "Meals", quotes included, and the total becomes 300.How Excel reads it
- It goes down A2:A8 and compares each cell with the condition, here the text in E2.
- For every cell that matches, it takes the cell in the same position of C2:C8.
- It adds those amounts. Rows that don't match are skipped.
The pairing is by position: the first cell of range goes with the first cell of sum_range, the second with the second. That's why the two ranges should start and end on the same rows.
Conditions on numbers
A comparison goes inside double quotes, operator and number together: ">200", "<=50". When the numbers being tested are also the numbers to add, the third argument can be left out.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Item | Amount | Over 200 | |
| 2 | Travel | Flight to Denver | 412 | 915 | |
| 3 | Software | Design suite | 60 | ||
| 4 | Meals | Team lunch | 86 | ||
| 5 | Travel | Rental car | 145 | ||
| 6 | Software | Video call plan | 150 | ||
| 7 | Meals | Client dinner | 214 | ||
| 8 | Travel | Flight to Austin | 289 |
">=150": 1065.Wildcards: part of a text
In a text condition, * stands for any run of characters and ? for exactly one. On this list, =SUMIF(B2:B8,"Flight*",C2:C8) adds the two flights (701), and "*lunch*" matches any item with the word lunch in it. To match a real asterisk or question mark, put a tilde in front of it: "~*".
The two mistakes that give a wrong total
Neither one shows an error, which is what makes them expensive. Both are live below. Fix the formula in the box and watch the cell.
1. The cell reference is inside the quotes. To compare against a cell, the operator goes in quotes and the cell is joined to it with &: ">"&E2. Written as ">E2", the condition is the text >E2. Cell E2 is never read, and no amount matches:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Category | Item | Amount | Over | Total | |
| 2 | Travel | Flight to Denver | 412 | 200 | 0 | |
| 3 | Software | Design suite | 60 | |||
| 4 | Meals | Team lunch | 86 | |||
| 5 | Travel | Rental car | 145 | |||
| 6 | Software | Video call plan | 150 | |||
| 7 | Meals | Client dinner | 214 | |||
| 8 | Travel | Flight to Austin | 289 |
&: =SUMIF(C2:C8,">"&E2) gives 915.2. The two ranges don't line up. Here the amounts start one row above the categories. Excel still pairs first cell with first cell, so every match picks up the amount from the wrong row:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Category | Item | Amount | Category | Total | |
| 2 | Travel | Flight to Denver | 412 | Travel | 300 | |
| 3 | Software | Design suite | 60 | |||
| 4 | Meals | Team lunch | 86 | |||
| 5 | Travel | Rental car | 145 | |||
| 6 | Software | Video call plan | 150 | |||
| 7 | Meals | Client dinner | 214 | |||
| 8 | Travel | Flight to Austin | 289 |
C1:C7, each Travel row gets the amount of the row above it: the header (text, so nothing is added), then 86 and 214. Make the third argument C2:C8.A related case from Microsoft's page: when sum_range is a different size from range, Excel doesn't stop with an error. It adds a block that starts at the first cell of sum_range and has the dimensions of range. Write both ranges with the same first and last row and you never have to think about it.
And the quoting rule, also from Microsoft: a text condition, or any condition with a comparison symbol, goes in double quotes ("Travel", ">200"). A plain number doesn't need them.
Practice: 3 exercises
Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in number won't pass.
In E2, add up everything spent on Meals.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Item | Amount | Meals total | |
| 2 | Travel | Flight to Denver | 412 | ||
| 3 | Software | Design suite | 60 | ||
| 4 | Meals | Team lunch | 86 | ||
| 5 | Travel | Rental car | 145 | ||
| 6 | Software | Video call plan | 150 | ||
| 7 | Meals | Client dinner | 214 | ||
| 8 | Travel | Flight to Austin | 289 |
Show the answer
=SUMIF(A2:A8,"Meals",C2:C8) gives 300.
SUMIF checks each cell of A2:A8 against "Meals" and adds the amount on the same row of C2:C8: 86 + 214.
E2 holds a limit. In F2, add up the expenses of that amount or more, so the total follows E2 when the limit changes.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Category | Item | Amount | Limit | Total | |
| 2 | Travel | Flight to Denver | 412 | 150 | ||
| 3 | Software | Design suite | 60 | |||
| 4 | Meals | Team lunch | 86 | |||
| 5 | Travel | Rental car | 145 | |||
| 6 | Software | Video call plan | 150 | |||
| 7 | Meals | Client dinner | 214 | |||
| 8 | Travel | Flight to Austin | 289 |
Show the answer
=SUMIF(C2:C8,">="&E2) gives 1065.
The operator is text, so it sits in quotes, and & glues the value of E2 to it: SUMIF receives >=150. With no third argument it adds the cells it tests: 412 + 150 + 214 + 289.
E2 holds a word. In F2, add up every expense whose item starts with that word.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Category | Item | Amount | Starts with | Total | |
| 2 | Travel | Flight to Denver | 412 | Flight | ||
| 3 | Software | Design suite | 60 | |||
| 4 | Meals | Team lunch | 86 | |||
| 5 | Travel | Rental car | 145 | |||
| 6 | Software | Video call plan | 150 | |||
| 7 | Meals | Client dinner | 214 | |||
| 8 | Travel | Flight to Austin | 289 |
Show the answer
=SUMIF(B2:B8,E2&"*",C2:C8) gives 701.
E2&"*" builds the condition Flight*: the word, then anything. Both flights match: 412 + 289.
Questions people ask
Why is my SUMIF returning 0?
Because the condition never matches. Check three things: a cell reference typed inside the quotes (">E2" instead of ">"&E2), ranges that don't start on the same row, and a text that isn't exactly the same in the data. Microsoft's COUNTIF page warns about spaces at the start or end of a cell, and the COUNTIF page here shows one live. The first two are live on this page.
What is the difference between SUMIF and SUMIFS?
SUMIF takes one condition. SUMIFS takes one or more, and a row has to meet all of them. The order of the arguments is different: in SUMIF the range to add comes last and is optional, in SUMIFS it comes first. With a single condition both give the same total: =SUMIFS(C2:C8,A2:A8,"Travel") is 846 on the list above, like the SUMIF.
Can SUMIF add up two categories at once?
Not with one condition. Add two SUMIFs: =SUMIF(A2:A8,"Travel",C2:C8)+SUMIF(A2:A8,"Meals",C2:C8) gives 1146 on the list above.
Where can I do SUMIF practice online?
On this page: the three exercises above run in the browser, are free and need no sign-up, and each one has its answer under "Show the answer". The formula playground is a blank sheet for trying your own data, and the free Excel test is 10 questions in a real spreadsheet.