Five SUMIF patterns that run my household expense tracker

L Jery — Thu, 22 Oct 2026 08:26:39 +0000

Every expense tracker I've ever kept alive longer than a month ran on the same handful of formulas. Here are the five that do the real work in mine — all plain SUMIF/SUMIFS, no macros, no add-ins.

1. Category totals

=SUMIF(log_category_range, "Groceries", log_amount_range)
One row per category, one formula each. This is the entire summary table. If your log's category column is a dropdown, this never breaks.

2. One month only

=SUMIFS(amount, date_range, ">="&DATE(2026,10,1), date_range, "<"&DATE(2026,11,1))
SUMIFS with DATE() bounds gives you a clean monthly slice without helper columns. Change the month and the whole summary follows.

3. Budget vs actual

Variance is just budget − actual, and execution rate is actual / budget. Two columns, zero cleverness — but as percentages at the top of the sheet, they're the part you actually look at.

4. Share of total

Each category total divided by the grand total. This is the number that ends arguments: not "we spend a lot on food delivery" but "food delivery is 18% of everything."

5. The biggest leak

=INDEX(category_range, MATCH(MAX(share_range), share_range, 0))
Pulls the name of your largest category automatically. Put it at the top of the summary; some months it's the only cell that matters.

These five patterns, plus a budget sheet and a debt snowball sheet, are the workbook I ended up with. There's a free sample (the budget sheet) and the full bilingual EN/中文 planner is $0.99 on my Ko-fi if you want the assembled version: https://ko-fi.com/s/3ba2ef96cd

What patterns run your tracker? Genuinely curious what other people automate.

The debt snowball, in spreadsheet formulas

L Jery — Wed, 21 Oct 2026 16:56:16 +0000

I keep seeing people ask how the "debt snowball" method works in practice, so here are the mechanics — and how I implemented them in a plain spreadsheet, no app subscription required.

The method in 30 seconds

List your debts from smallest balance to largest. Pay the minimum on everything, and throw every spare dollar at the smallest one. When it's gone, roll its payment into the next debt. The "snowball" is the payment growing as debts fall. It's not the mathematically cheapest order (that's the avalanche — highest APR first), but it's the one people actually stick to, because the first win comes fast.

The three formulas that do the work

Ordering. RANK(balance, all_balances, 1) gives each debt its snowball position, smallest = 1.

Payoff time. For a debt with monthly rate r, payment p and balance B, the months to clear it are approximately NPER(r, -p, B). With APR sitting in a cell: =NPER(APR/12, -payment, balance). Raise the "extra payment" input and NPER drops — that feedback loop is the whole point of a planner: change one number, watch every payoff date move.

Interest estimate. Total paid minus principal: payment × months − balance. It's a simplification (payments step up as the snowball rolls), and any honest template should say so.

The same workbook pattern runs the budget side: SUMIF(category_range, category, amount_range) rolls a raw expense log into per-category totals automatically, and budget-vs-actual is just variance and execution-% columns on top.

The template I ended up with

I built mine as three sheets — a monthly budget (4 income and 12 expense categories), an expense log with a SUMIF summary and chart, and the snowball sheet above — with bilingual English/中文 headers, because half my family refuses to read English spreadsheets. Yellow cells are inputs, blue cells are formulas.

If it's useful to anyone else: there's a free sample (just the budget sheet) and the full planner is $0.99 on my Ko-fi: https://ko-fi.com/s/3ba2ef96cd

Estimates are simplified — it's a planning tool, not financial advice.

DEV Community: L Jery