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.
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.