When category totals in an expense sheet do not add up to the grand total, the cause is almost always in the data rather than the formula: a stray space, a different spelling, or an amount stored as text. Check the category column and the amount column before you rewrite anything.
Start with the sum of the parts
Add up your category totals and compare the result with the grand total of the whole amount column. Say the grand total is £2,310.40 and your category totals add to £2,186.15. The difference, £124.25, is spending that matched no category. Find it by listing everything your category formulas missed, and it will usually be a handful of rows.
| Row text in category column | What the formula sees | Matches Groceries? |
|---|---|---|
| Groceries | Groceries | Yes |
| groceries | groceries | Yes, SUMIFS ignores case |
| Groceries (with a trailing space) | Groceries plus a space | No |
| Grocery | Grocery | No |
How do you find the missing rows?
Add a helper column with =IF(COUNTIF($H$2:$H$20,B2)=0,"MISSING","") where H2:H20 holds your official category list and B2 is the first category cell. Fill it down. Any row marked MISSING has a category that is not in the list, so you can see the typo or extra space straight away.
- Trailing and leading spaces: fix with =TRIM(B2) in a helper column
- Different spellings: pick one name and use Find and Replace
- Blank categories: filter for blanks and fill them in
- Merged cells in the data area: unmerge them
Do amount cells cause the same trouble?
Yes. If an amount was pasted with a pound sign attached, it may be text. A test is =ISNUMBER(C2). To convert in bulk, use =VALUE(SUBSTITUTE(C2,"£","")) in a helper column, fill down and paste as values. Imagine three text amounts of £45.00, £12.50 and £66.65. Left as text they add up to zero, but converted they give 45 + 12.5 + 66.65 = 124.15, which is most of the gap in the example above.
What should a monthly report formula look like?
- Put the month's start date in a cell, say F1, and the next month's start in F2.
- Write =SUMIFS($C$2:$C$500,$B$2:$B$500,"Groceries",$A$2:$A$500,">="&$F$1,$A$2:$A$500,"<"&$F$2).
- Copy the formula down the category list, changing the category text to a cell reference.
- Add a final row that sums all categories and compare it with the full-month total.
A different way to avoid the rebuild
If the monthly check eats an evening, a tool that builds reports straight from transactions removes this class of bug. SpendixAI is built around India and INR, so it does not serve pound accounts, but its reports come from the transactions and its categories are managed in one place.
How do you stop it happening again?
Lock the structure and let only the data change. Keep the category list on its own tab, point the dropdown at it, and treat that list as the single source of truth. If you add a new category, add it to the list first. Check totals at the end of every month using the sum-of-parts test: categories added up should equal the grand total. If the gap is not £0.00, spend five minutes finding it that day, while you still remember what you pasted.
Are there simpler ways to total a category?
A pivot table summarises by category without any typed formulas. Select the data, choose Insert, then PivotTable, drop Category into Rows and Amount into Values. It is not immune to the same data problems, since a trailing space still creates a separate row, but those problems become visible at once as extra lines in the list. Refresh it after pasting new rows, and compare its grand total with your amount column.
Checklist
- Categories compared with an official list
- Spaces trimmed
- Amounts all pass ISNUMBER
- Category total equals grand total
- Dates entered as real dates
Useful ideas start with a clearer picture of your own spending.
Explore your reports

