Spreadsheets

Sum expenses by category in Google Sheets with SUMIF

Sum expenses by category in Google Sheets with SUMIF: whole-column ranges that grow, copying down a category list, ARRAYFORMULA, QUERY and a circular-reference trap.

Editorial illustration for Sum expenses by category in Google Sheets with SUMIF

In Google Sheets, SUMIF totals expenses by category with =SUMIF(C:C,"Groceries",E:E), where column C holds categories and column E holds amounts. Whole-column ranges grow as you add rows, so the formula never needs updating. To total every category, put the names in a list and copy the formula down with the criteria as a cell reference.

How does the basic formula work?

SUMIF(range, criterion, sum_range) scans the range for cells that match the criterion and adds the numbers from the same rows in the sum range. The text header in row 1 is ignored because the heading is not a number. Criteria in quotes are not case sensitive.

  • =SUMIF(C:C,"Groceries",E:E) adds up every Groceries row.
  • =SUMIF(C:C,A2,E:E) does the same using the category written in A2.
  • =SUMIF(C:C,"<>Rent",E:E) adds up everything except Rent.

How do I total every category by copying down a list?

Put your category names in A2:A10 on a Summary tab. If your transactions sit on a tab called Data, the formula in B2 is as follows, and you copy it down beside each category.

  • =SUMIF(Data!$C:$C,$A2,Data!$E:$E)
  • The dollar sign before C and E locks those columns. The $A2 locks the column but lets the row change as you copy down.

For a list that fills itself, use =UNIQUE(FILTER(Data!C2:C,Data!C2:C<>"")) in A2. Then in B2 use =ARRAYFORMULA(IF(A2:A="","",SUMIF(Data!C:C,A2:A,Data!E:E))) for all categories at once, with the IF keeping the empty rows blank.

What does a worked example look like?

Say the Data tab holds these rows: Groceries ₹1,450, Fuel ₹2,200, Groceries ₹980, Rent ₹18,000 and Fuel ₹1,800. The totals are Groceries 1,450 + 980 = ₹2,430, Fuel 2,200 + 1,800 = ₹4,000, and Rent ₹18,000. Altogether 2,430 + 4,000 + 18,000 = ₹24,430, which must match =SUM(Data!E:E).

CategoryRowsTotal
Groceries2₹2,430
Fuel2₹4,000
Rent1₹18,000
All5₹24,430

Is there a faster way for many categories?

QUERY can group and total in one cell: =QUERY(Data!A2:E,"select C, sum(E) where C is not null group by C",0). It lists each category once with its total and updates as rows are added. The column letters inside the quotes refer to columns of the range you gave, so keep the range starting at column A.

Why does my total look too small?

  • Amounts pasted from a bank file are sometimes text. Check with =COUNT(E2:E) against =COUNTA(E2:E), and convert text with =VALUE(SUBSTITUTE(E2,",","")).
  • A category has a trailing space or a spelling variant. Filter the column to see each distinct value.
  • The category sits in a different column than the formula expects.

How do I limit the range to keep the sheet fast?

Whole-column references are convenient, but on a sheet with many thousands of rows and dozens of formulas they can slow things down. A middle route is a bounded range that is deliberately larger than you need, such as C2:C5000, which still covers growth for a long time and keeps calculations quick.

  • Use whole columns for small personal sheets and quick checks.
  • Use bounded ranges, such as $C$2:$C$5000, for large sheets.
  • Remember that rows added beyond a bounded range are silently ignored, so set a reminder to widen it.

How do I add a month filter on top?

When you want the category total for one month, move from SUMIF to SUMIFS, which handles several conditions. Put the first day of the month in D1. The formula below totals one category in that month, and it works with whole columns in Sheets.

  • =SUMIFS(Data!$E:$E,Data!$C:$C,$A2,Data!$A:$A,">="&$D$1,Data!$A:$A,"<"&EDATE($D$1,1))
  • Note that SUMIFS lists the sum range first, while SUMIF lists it last.

Make sure the dates in column A of the Data tab are real dates. In Sheets, a real date aligns to the right by default, and a text date aligns to the left.

When should I stop using a sheet?

A sheet does this job well for one account. When you are importing statements from several accounts every month, SpendixAI can bring them in from PDF, CSV or Excel and group spending by category without formulas, which may be worth a look then.

SPENDIXAI JOURNAL

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

Explore your reports