Spreadsheets

SUMIF formula to total expenses by category in Excel

Use the SUMIF formula in Excel to total expenses by category: exact syntax, a category summary table, wildcards, the SUMIFS argument-order trap and a check total.

Editorial illustration for SUMIF formula to total expenses by category in Excel

To total expenses by category in Excel, use SUMIF with three arguments: the range that holds the categories, the category you want, and the range that holds the amounts. If categories are in C2:C500 and amounts in E2:E500, =SUMIF($C$2:$C$500,"Groceries",$E$2:$E$500) returns the total spent on Groceries.

How does SUMIF read its arguments?

The syntax is SUMIF(range, criteria, sum_range). Excel looks down the range, finds the rows where the text matches the criteria, and adds the matching numbers from the same rows in sum_range. The two ranges must be the same size and start on the same row. The dollar signs lock the ranges so they do not drift when you copy the formula.

  • range: $C$2:$C$500, the Category column.
  • criteria: "Groceries", or a cell such as G2 that holds the word.
  • sum_range: $E$2:$E$500, the Amount column.

How do I build a summary table that lists each category once?

Put the heading Category in G1 and Total in H1. Type your category names down column G. In Excel 365 or 2021, you can list them automatically with =UNIQUE(FILTER($C$2:$C$500,$C$2:$C$500<>"")) in G2, which spills the distinct names and skips blank rows. In older versions, copy the Category column to column G and use Data, Remove Duplicates.

Then in H2 enter the formula below and copy it down beside every category.

  • =SUMIF($C$2:$C$500,G2,$E$2:$E$500)
  • With a spilled list in Excel 365, you can point at the whole list instead, as in =SUMIF($C$2:$C$500,G2#,$E$2:$E$500), and the totals spill too.

What does a worked example look like?

Say three Groceries rows read ₹2,450, ₹1,180 and ₹3,260, and two Fuel rows read ₹2,000 and ₹1,500. The formula for Groceries returns 2,450 + 1,180 + 3,260 = ₹6,890, and for Fuel 2,000 + 1,500 = ₹3,500. The two totals together are 6,890 + 3,500 = ₹10,390, which the check below should reproduce.

CategoryRowsTotal
Groceries3₹6,890
Fuel2₹3,500
Total5₹10,390

How do I know no row was missed?

Add a check cell: =SUM($E$2:$E$500)-SUM(H2:H20). It should show 0. If it does not, some amounts have a category that is not in your summary list, a misspelling, or a trailing space such as "Fuel ". Look for the odd category with a filter on column C. Amounts stored as text are another cause, because SUMIF ignores text. A cell that is a real number is right-aligned by default.

Can I match part of a category name?

Yes, with wildcards. =SUMIF($C$2:$C$500,"Food*",$E$2:$E$500) adds every category starting with Food, such as Food - Dining and Food - Groceries. A question mark stands for exactly one character. To match a literal asterisk or question mark, put a tilde before it. To add everything except one category, use "<>Fuel" as the criteria.

What if my amounts include credits and refunds?

Decide on one convention before you sum. If refunds are negative numbers in the same Amount column, SUMIF nets them against spending automatically: a ₹1,100 refund under Clothing, recorded as -1,100, reduces the Clothing total. If refunds sit in a separate Credit column, build a second SUMIF on that column and subtract: =SUMIF($C$2:$C$500,G2,$E$2:$E$500)-SUMIF($C$2:$C$500,G2,$F$2:$F$500).

Mixing both conventions in one sheet is the usual cause of a total that is out by exactly twice a refund.

How is this different from SUMIFS?

SUMIFS handles more than one condition, and it puts the sum range first: SUMIFS(sum_range, criteria_range1, criteria1, ...). SUMIF puts it last, and it is optional. If you swap them by habit, you get zero or an error that looks like a correct answer. Say the order out loud when you write either one.

A sheet like this works well for one account. When you have several accounts and months of statements, SpendixAI can import them and group spending by category for you, which is worth considering once keeping the sheet turns into a monthly chore.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports