Sum Expenses by Category
Add up all transactions that match a specific category, like "Groceries" or "Entertainment".
Formula
=SUMIF(category_range, "Category Name", amount_range) How It Works
SUMIF adds up values in one range based on a condition in another range. For budgeting, this means you can total all expenses that match a specific category.
Syntax
=SUMIF(range, criteria, sum_range)- range: The cells containing categories (e.g., A2:A100)
- criteria: The category to match (e.g., “Groceries”)
- sum_range: The cells containing amounts to sum (e.g., B2:B100)
Example
| Transaction | Category | Amount |
|---|---|---|
| Walmart | Groceries | $85.50 |
| Netflix | Entertainment | $15.99 |
| Trader Joe’s | Groceries | $62.30 |
| Electric bill | Utilities | $120.00 |
Formula: =SUMIF(B2:B5, "Groceries", C2:C5)
Result: $147.80
Variations
Case-Insensitive Match
=SUMIF(B:B, "groceries", C:C)Google Sheets SUMIF is case-insensitive by default.
Using a Cell Reference
=SUMIF(B:B, E1, C:C)Where E1 contains the category name - useful for dropdown menus.
Partial Match with Wildcards
=SUMIF(B:B, "*Food*", C:C)Matches any category containing “Food” (e.g., “Fast Food”, “Food Delivery”).
Pro Tips
-
Use named ranges for cleaner formulas:
=SUMIF(Categories, "Groceries", Amounts) -
Lock ranges with $ when copying:
=SUMIF($B$2:$B$100, D2, $C$2:$C$100) -
Combine with data validation for consistent category names
Common Errors
- #VALUE!: Ranges have different sizes - make sure category_range and sum_range have the same number of rows
- Returns 0 unexpectedly: Check for typos or extra spaces in category names