Track a monthly budget in Excel with SUMIFS
A budget is two lists: what you planned to spend, and what you actually spent. The planned amounts sit in one table, the spending in a log of dated rows. SUMIFS adds up the log by category, so each row of the budget shows planned, actual, and what is left — and flags the category that went over.
| Checked | |
|---|---|
| Apps | Excel, Google Sheets, LibreOffice |
| Versions | LibreOffice 24.2.7.2 (recalculation) · Python 3.12.3 · openpyxl 3.1.2 |
| Read time | 8 minutes |
Measured result — from the recalculated file
| Planned for November | $3,005.00 |
| Actually spent | $2,782.69 |
| Left over | $222.31 |
| Groceries: planned $450.00, spent $487.15 | Over budget by $37.15 |
| Rent: planned $1,450.00, spent $1,450.00 | Exactly on budget |
Download the spreadsheet: monthly-budget-tracker.xlsx (6,799 bytes)
The file that was recalculated and checked. It contains the budget table with the SUMIFS formulas and the 20-row expense log for November 2026.
1What you need
- Excel, Google Sheets, or LibreOffice. SUMIFS and IF work the same in all three for this tutorial.
- The file above, or a blank workbook if you want to type the formulas in yourself.
2The budget table
The “November budget” sheet holds eight categories and the amount planned for each. The total planned is $3,005.00:
| Category | Planned |
|---|---|
| Rent | $1,450.00 |
| Groceries | $450.00 |
| Transport | $180.00 |
| Utilities | $210.00 |
| Dining out | $150.00 |
| Entertainment | $90.00 |
| Clothing | $75.00 |
| Savings | $400.00 |
3The expense log
The “Expenses” sheet is a plain log: date, category, note, amount. Twenty rows, 2 to 20 November 2026. The category in each row must match a category in the budget table exactly — that match is what the formula in the next step uses. The biggest single row is the $1,450.00 rent payment on 9 November; the smallest is a $15.00 streaming subscription on 17 November.
4SUMIFS adds up each category
SUMIFS adds the cells in a range that meet multiple criteria. Its first argument is the range to add; the pairs after it are the range to test and the test to apply (SUMIFS function — Microsoft Support). The Groceries row reads:
C5 =SUMIFS(Expenses!$D$2:$D$21, Expenses!$B$2:$B$21, $A5)In words: add the amounts in the expense log (column D) where the category in the log (column B) equals the category in this row (column A). For Groceries the six matching rows — $86.40, $112.75, $64.30, $93.60, $71.20 and $58.90 — add to $487.15. Copy the formula down and every category fills in from the same log. No category is typed twice: change an amount in the log and the budget updates.
5What is left, and the over-budget flag
Two more columns finish the table. Remaining is planned minus actual:
D5 =B5-C5And the flag is a plain IF test:
E5 =IF(C5>B5, "Over budget", "Within budget")Groceries is the only category over: $487.15 spent against $450.00 planned, so remaining is −$37.15 and the flag reads “Over budget”. Rent and Savings land exactly on plan, at $0.00 remaining. Transport has the most left, $96.75 of $180.00 unspent.
6The totals row
The bottom row sums each column:
B12 =SUM(B4:B11)
C12 =SUM(C4:C11)
D12 =B12-C12Planned $3,005.00, spent $2,782.69, left $222.31. The totals are sums of the category rows, not a second SUMIFS over the log — the two must agree, and in the checked file they do: the log rows add to $2,782.69 as well.
Traps
SUMIF and SUMIFS take their arguments in a different order. In SUMIFS the sum range comes first; in SUMIF it comes third. Microsoft calls this out as a common source of problems. Copying a SUMIF formula and adding an “S” without reordering the arguments sums the wrong range.
Ranges of different heights. The criteria range must contain the same number of rows as the sum range. SUMIFS(Expenses!$D$2:$D$21, Expenses!$B$2:$B$20, $A5) drops the last expense row and undercounts the month.
A category that does not match exactly. SUMIFS matches text case-insensitively, but “Groceries ” with a trailing space is a different category from “Groceries” and its rows are never counted. If you paste a log from elsewhere, clean the category column first — the TRIM, CLEAN, and PROPER tutorial shows how.
Text criteria need quotes; cell references do not. Writing SUMIFS(..., Expenses!$B$2:$B$21, "Groceries") works, and so does SUMIFS(..., Expenses!$B$2:$B$21, $A5). Writing "$A5" in quotes looks for a category literally called $A5 and returns 0.
How this was checked
The workbook was built with openpyxl 3.1.2 (formulas only, no typed-in results), recalculated by LibreOffice 24.2.7.2 in headless mode, and read back with openpyxl in data-only mode. The expected values were computed independently in Python 3.12.3 from the same 20 expense rows: every category actual and remaining, the status flag, and all three totals matched within $0.01. The log rows were also summed directly in Python and agreed with the SUMIFS totals at $2,782.69.
Sources
- SUMIFS function — Microsoft Support — syntax, the sum range coming first, and the warning that SUMIF and SUMIFS order their arguments differently.
- Sum values based on multiple conditions — Microsoft Support — a worked SUMIFS example with two conditions.