Excel function guide · Conditional sums & counts
SUMIF function in Excel
SUMIF adds the values in a range that meet one condition, for example all the expenses in the Travel category. You give it the range to test, the condition, and the range to add.
Syntax
=SUMIF(range, criteria, [sum_range])
| Argument | Required? | What it means |
|---|---|---|
| range | Required | The cells to test against the condition. |
| criteria | Required | The condition: a value ("Travel"), a cell, a comparison (">100") or a wildcard pattern ("T*"). |
| sum_range | Optional | The cells to add. Leave it out to add the tested cells themselves. |
SUMIF examples
Every example uses this small table. The results are what Excel shows in the cell.
| A | B | C | |
|---|---|---|---|
| 1 | Category | Item | Amount |
| 2 | Travel | Train to Leeds | 86 |
| 3 | Meals | Client lunch | 142 |
| 4 | Travel | Hotel, 2 nights | 310 |
| 5 | Office | Printer paper | 24 |
| 6 | Meals | Team breakfast | 58 |
| 7 | Travel | Taxi | 31 |
1. Total for a category
=SUMIF(A2:A7, "Travel", C2:C7)
Result: 427
Adds the Amount on every Travel row: 86 + 310 + 31.
2. Amounts over 100
=SUMIF(C2:C7, ">100")
Result: 452
No sum_range: SUMIF adds the tested cells themselves, 142 + 310.
3. Wildcard match
=SUMIF(B2:B7, "T*", C2:C7)
Result: 175
Items starting with T: Train to Leeds, Team breakfast and Taxi. * stands for any characters, ? for exactly one.
Try it now
Practise SUMIF in a live sheet.
Type each formula yourself. Answers are checked by the same engine as the full course. No account needed.
Step 1 of 3
In E3, total the amounts for the category in E2.
Common SUMIF mistakes
Mixing up the order with SUMIFS
SUMIF is (range, criteria, sum_range); SUMIFS starts with the sum range. If you need a second condition, switch to SUMIFS rather than nesting.
Forgetting sum_range
=SUMIF(A2:A7, "Travel") adds the Category cells, which are text, so it returns 0. Add C2:C7 as the third argument.
A sum_range of a different size
SUMIF doesn't complain if sum_range is a different size: it quietly uses a block the same size as range, starting at sum_range's first cell. Keep both ranges the same size so the result is what you expect.
Comparisons without quotes
Write ">100" in quotes. To compare with a cell, join it: ">"&E2.
Practise this in Excel Quest
COUNTIF & SUMIFFree
Course 5 · Conditional Aggregation · 11 min
Count and total only the rows that match — then build a region summary that reconciles to the total.
This lesson is free: create an account and it opens straight away.