Text without quotes
=SUMIF(B2:B8,Travel,C2:C8)
The checker says: Text in a formula needs double quotes: "Travel".
Without quotes, Excel reads Travel as a name it doesn’t know and shows #NAME?.
Excel function
SUMIF in Excel
SUMIF adds the numbers that meet one condition, such as every expense in the Travel category. For two or more conditions, use SUMIFS.
Syntax
=SUMIF(range, criteria, [sum_range])
| Argument | What it does |
|---|---|
| range | The cells to check, such as the categories. |
| criteria | The condition: text like "Travel", a comparison like ">100", or a cell such as F2. |
| sum_range | Optional. The cells to add. Leave it out and SUMIF adds the cells it checks. |
Here’s what that formula is doing.
=SUMIF(B2:B8,"Travel",C2:C8) checks each category in B2:B8 and adds the amount next to every Travel row: 42.50 + 310 + 27 = 379.50.
Try it with real data.
Type a formula, then check it. Hints step in when you’re stuck, and the last one shows the answer.
Example data
Task 1
In B10, total the Travel expenses.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | Category | Amount | Paid | Category | Total | |
| 2 | Taxi to airport | Travel | 42.50 | Yes | Meals | ||
| 3 | Team lunch | Meals | 86.00 | No | |||
| 4 | Hotel, 2 nights | Travel | 310.00 | Yes | |||
| 5 | Printer ink | Office | 38.90 | Yes | |||
| 6 | Client dinner | Meals | 124.00 | Yes | |||
| 7 | Train ticket | Travel | 27.00 | No | |||
| 8 | Notebooks | Office | 15.60 | No | |||
| 9 | |||||||
| 10 | Travel total | (answer cell) | |||||
| 11 | Unpaid total |
Scroll the sheet sideways to see every column.
Nice. Try the next task
Example data
Task 2
In G2, total whichever category is typed in F2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | Category | Amount | Paid | Category | Total | |
| 2 | Taxi to airport | Travel | 42.50 | Yes | Meals | (answer cell) | |
| 3 | Team lunch | Meals | 86.00 | No | |||
| 4 | Hotel, 2 nights | Travel | 310.00 | Yes | |||
| 5 | Printer ink | Office | 38.90 | Yes | |||
| 6 | Client dinner | Meals | 124.00 | Yes | |||
| 7 | Train ticket | Travel | 27.00 | No | |||
| 8 | Notebooks | Office | 15.60 | No | |||
| 9 | |||||||
| 10 | Travel total | 379.50 | |||||
| 11 | Unpaid total | 128.60 |
Scroll the sheet sideways to see every column.
That’s both. Practise the whole course free.
Common mistakes, and what our checker says.
Each message below is the checker’s real reply to that formula on the example data.
Ranges the wrong way round
=SUMIF(C2:C8,"Travel",B2:B8)
The checker says: The ranges are the wrong way round. SUMIF checks the first range against the condition, then adds the matching cells in the third.
Check the categories first, then add the amounts.
Leaving out the range to add
=SUMIF(B2:B8,"Travel")
The checker says: SUMIF needs a third argument here: the range to add. With only two, it adds the cells it checks.
The categories are text, so the total comes out as 0.
A cell address in quotes
=SUMIF(B2:B8,"F2",C2:C8)
The checker says: Use F2 as the criteria, so the total follows whatever category is typed there.
With quotes, SUMIF looks for the text F2. Without them, it uses whatever is in cell F2.
Questions
What’s the difference between SUMIF and SUMIFS?
SUMIF takes one condition. SUMIFS takes several, and puts the range to add first: =SUMIFS(C2:C8,B2:B8,"Travel",D2:D8,"No").
How do I add amounts over 100?
Put the comparison in quotes: =SUMIF(C2:C8,">100"). With one range, SUMIF checks and adds the same cells.
Is SUMIF case-sensitive?
No. "travel" and "Travel" match the same cells.
SUMIF is part of module 7 of Excel Foundations: Count and add with conditions.
Start practising now.
Excel Foundations is free, with no sign-up. Your progress is saved in this browser.