Total one expense category (SUMIF)
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
The problem
Problem statement
Expenses are recorded line by line in A2:B6. The category to total is in E1. Put its total in the Total cell E2.
The category comes from E1 — the same formula has to answer for Meals or Office.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | E | |
|---|---|---|---|---|
| 1 | Category | Amount | Category | Travel |
| 2 | Travel | 1200 | Total | |
| 3 | Office | 800 | ||
| 4 | Travel | 450 | ||
| 5 | Meals | 300 | ||
| 6 | Travel | 950 |
Notes
SUMIF(criteria_range, criteria, sum_range) is the whole job. The mistake that survives review longest is forgetting the third argument: with two arguments SUMIF totals the criteria column itself, which for a text category returns 0 and looks like "no expenses".
How to approach
Your answer goes in E2.
- Target cell is E2
- SUMIF takes the category column, the category, then the amounts
- All three ranges must be the same height
- Shape: =SUMIF(?, ?, ?)
Function guide
FAQ
How do I total a category in Excel?
=SUMIF(category_column, category, amount_column). All three ranges must cover the same rows.
Why does SUMIF return 0 for a category I can see?
Usually the third argument is missing, so it summed the text column. Check that the sum range points at the amounts.