Expense tracker in Google Sheets: the one SUMIFS you need, explained

The whole tracker is a log with a dropdown and one SUMIFS copied around. Here's the SUMIFS in pieces, then the three views that come out of it: this month, against limits, and the year.

Contents

The log

Four columns: date, description, category, amount. A fifth for payment method if you care. The category cell gets a dropdown (Data → Data validation → Dropdown from a range) fed by a list on a Setup sheet. That list is the backbone: every total is a SUMIFS against it.

Keep the log flat. No subtotals in the middle, no blank separator rows, no merged cells — every formula that reads it assumes one transaction per row from row 5 down to a row you decide, say 1000.

The SUMIFS, in pieces

total for one category in one month

=SUMIFS(Log!$D$5:$D$1000,                          ← what to add: amounts
      Log!$C$5:$C$1000, $A6,                      ← where category = this row's category
      Log!$A$5:$A$1000, ">="&$B$2,                ← and date ≥ first of the month
      Log!$A$5:$A$1000, "<="&EOMONTH($B$2,0))     ← and date ≤ last of the month

$B$2 holds the first day of the month you’re looking at. EOMONTH($B$2, 0) is that month’s last day, whatever the month. The & joins the comparison text to the date, which is how SUMIFS wants a “greater than a cell” condition written.

Copy this down a column of categories and you have “this month by category”. Add =SUM(…) at the bottom and =IF($B$16=0,0,B6/$B$16) beside each row for the share.

the wrong way to do dates

=SUMIFS(…, Log!A:A, "September")     ← dates aren't text; this matches nothing

View 1: this month, with bars

Select the totals column and add a data bar (Format → Conditional formatting → Color scale or, in Excel, Data Bars). The biggest category fills the cell. If you want it named:

the biggest category

=INDEX(A6:A15, MATCH(MAX(B6:B15), B6:B15, 0))

MAX finds the largest total, MATCH finds which row it’s on, INDEX returns the category name beside it.

View 2: against limits

Give each category a monthly limit on the Setup sheet. Then three columns: spent (the SUMIFS above), left (=limit − spent), and used (=IF(limit=0, 0, spent/limit)). Conditional format “left” red when negative, and put a data bar on “used” that ends at 100% — and a rule that turns the number red when it’s over 1.

View 3: twelve months by category

A grid: months down the side, categories across the top. Each cell is the same SUMIFS with the category taken from the column header and the month from the row.

one cell of the month × category grid

=SUMIFS(Log!$D$5:$D$1000, Log!$C$5:$C$1000, B$4, Log!$A$5:$A$1000, ">="&$A5, Log!$A$5:$A$1000, "<="&EOMONTH($A5,0))

Mixed references again: B$4 (header, column moves), $A5 (month, row moves). Type it once in the top-left cell and fill the whole grid. For the month column:

twelve months back from the report month

A5:  =EOMONTH($B$2,-12)+1
A6:  =EOMONTH(A5,0)+1        … first day of the next month; copy down

An average row underneath: =IFERROR(AVERAGEIF(B5:B16, ">0"), 0) — months with nothing logged don’t drag the average down.

Why the ranges stop at 1000

Log!$D$5:$D$1000 instead of Log!D:D. Whole-column references are convenient and make Google Sheets recalculate slowly once there are a few dozen of them on a sheet. A stated row is faster and honest: write it on the sheet, and tell people to raise it if they log more.

Greying out other months

One conditional-format rule on the log makes the current month obvious:

grey rows outside the report month

=AND($A5<>"", OR($A5<$B$2, $A5>EOMONTH($B$2,0)))

Those rows still feed the twelve-month grid; they’re just visually out of the way.

Outlay is this tracker built out — log, monthly panel, limits, twelve-month grid — with a sheet that explains each formula, and the free version is the log with its panel.