Budget sheets break for a few repeatable reasons: numbers stored as text so SUM skips them, deleted rows that leave #REF!, ranges that stop short of new rows, and hidden or filtered rows that SUM still counts. Each has a quick fix, shown below with a before and after.
Why does SUM ignore some of my amounts?
Numbers pasted from a bank site or typed with a ₹ sign or a stray space are often stored as text. SUM skips them without any error, so the total is quietly too low.
- Before: =SUM(C2:C200) shows ₹41,300 while the amounts add up to ₹44,800 by hand.
- Find them: =COUNTA(C2:C200)-COUNT(C2:C200) counts text entries. Anything above 0 is a problem.
- After: select the column, use Data, Text to Columns, Finish, or multiply by 1 with Paste special, then SUM matches.
The numbers are invented, but the pattern is real: a total that is lower than it should be, with no error to warn you.
What causes #REF! after I delete a row?
A formula such as =C5+C6 points at specific cells. Delete row 6 and that reference has nowhere to go, so the formula turns to #REF!. A SUM over a range, such as =SUM(C2:C20), simply shrinks and does not error.
- Before: =C5+C6+C7 and a deleted row 6 gives #REF!.
- After: =SUM(C5:C7). Deleting a middle row keeps it working.
- If you have already broken it, use Undo at once; if not, retype the formula and check any cell it feeds.
Why do new rows not appear in totals?
A range such as C2:C100 stops at row 100. When your data grows to row 140, the extra rows are not counted. Two fixes:
- Use a bigger fixed range: =SUM($C$2:$C$2000), and keep the empty rows below.
- Turn the data into a table (in Excel, select it and press Ctrl+T). Formulas that refer to the table grow with it. In Google Sheets, use an open range such as C2:C.
Why does the total ignore my filter?
SUM adds hidden and filtered-out rows too. When you filter to one category and look at the total, it still shows everything. Use =SUBTOTAL(109,C2:C2000), which counts only the visible rows. The number 109 means sum, ignoring rows hidden or filtered.
Other quiet failures to check
- A category typed as 'Food ' with a trailing space will not match 'Food' in SUMIFS. Clean with =TRIM().
- Dates stored as text make SUMIFS with date conditions return 0.
- IFERROR hides errors, but it also hides real problems. Use it only after the cause is understood.
- Manual calculation mode: if numbers do not change when you edit, check Formulas, Calculation Options, Automatic.
One more warning is the circular reference message. It means a formula refers to its own cell, usually because a total row was copied into the data range. Excel points at the cell involved; move the total outside the range it adds up and the warning disappears.
A five-minute audit you can run today
- Count text numbers in the amount column with =COUNTA(C2:C2000)-COUNT(C2:C2000).
- Search the sheet for #REF! with Find, searching in Values.
- Check that your total ranges reach your last row of data.
- Remove any filter, then compare SUM and SUBTOTAL(109,...) for the same column; they should agree.
- Add the check cell comparing category totals with the grand total.
If all five pass, your sheet is sound. If two or more fail, fix them before reading anything into last month's numbers.
When is it time to switch?
Fixing a broken formula once is routine. If you fix a different one every month, or you no longer trust the totals without a manual check, you are maintaining a program, not tracking money. An app that stores each transaction as a record and builds reports from it avoids cell-reference errors altogether. SpendixAI has a library of 25 ready-made reports and a Report builder for filter and group, with CSV download. The cost is flexibility, so compare before you leave.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

