Spending per category with SUMIF
PremiumIn F2, total the amounts for the category in E2 with SUMIF. Lock the two ranges with $, so the formula still covers the whole list when you fill it down to F4.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | Category | Amount | Category | Spent | ||
| 2 | Movie tickets | Fun | 180 | Food | |||
| 3 | Supermarket | Food | 312 | Transportation | |||
| 4 | Bus pass | Transportation | 375 | Fun | |||
| 5 | Bakery | Food | 289 | ||||
| 6 | Gas | Transportation | 450 | ||||
| 7 | Takeout | Food | 199 | ||||
| 8 |
Premium lesson
Unlock Budget and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Your first month for $9.50 $19
50% off your first month when you join today.
Then $19/month. Cancel any time, and get a full refund within 14 days if it isn't for you.
Why this works
SUMIF checks the categories in B2:B7, keeps the rows that match E2 and adds up their amounts from C2:C7. Food is 312 + 289 + 199 = 800. The $ signs keep both ranges in place, and E2 has none, so it moves down to E3 and E4 when you fill the formula down.
Common mistake: Leaving out the $. Filled down to F4, the ranges become B4:B9 and C4:C9, the movie tickets in row 2 drop out, and Fun shows 0.
The answer
- F2
=SUMIF(1$B$2:$B$7,2E2,3$C$2:$C$7)
In plain words: Add up 3the amounts in C2:C7 on the rows where 1the category in B2:B7 is 2the one in E2.