Total Expenses by Category Automatically in Google Sheets (SUMIFS)
Group expenses into category totals that update when values change.
Download practice data · CSVStart the exerciseA published video is not available for this version yet. The complete written exercise and data are below.
Try it yourself
Import at A1. Put Food, Transport and Tools in D2:D4; enter the formula in E2 and copy down. Expect 42, 18, 35. Change B2 from 12 to 22: Food becomes 52.
=SUMIFS($B$2:$B$8,$A$2:$A$8,D2)Detailed steps
- 1. Keep amounts as numbers
Our fictional entries are in A two through B eight. Column A contains categories, and column B contains numeric amounts. A dollar display is formatting; the underlying values need to stay numbers.
- 2. List the categories once
In column D, list the categories you want to summarize: Food, Transport, and Tools. Click E two beside Food. This category label will become the criterion for the first total.
- 3. Enter SUMIFS
Start with SUMIFS. The sum range is B two through B eight, then A two through A eight is the category range, and D two is the category to match. Add dollar signs to keep both source ranges fixed.
- 4. Copy the formula down
Press Enter, then copy E two down to E four. D two changes to D three and D four while the source ranges stay fixed. The totals are forty two, eighteen, and thirty five dollars.
- Three details to check
Use consistent category labels and check for extra spaces in imported data. Keep amounts numeric. Extend both source ranges when adding entries beyond row eight. What would you summarize next: expenses, sales, or hours worked?
Preview the input data
| Category | Amount (USD) |
|---|---|
| Food | 12 |
| Transport | 8 |
| Food | 18 |
| Tools | 20 |
| Food | 12 |
| Transport | 10 |
| Tools | 15 |
How to open the CSV
In a blank Google Sheet choose File → Import → Upload and select the downloaded CSV. Use comma as the separator. Start at A1 unless the exercise specifies another location. CSV contains input data; add formulas and controls using the steps above. Files are hosted directly here and require no shared personal Drive document.
Fictional demo data. Formulas use English function names and commas between arguments. Other spreadsheet locales may require semicolons. Check date recognition after importing.