If you keep your transactions in a spreadsheet, SUMIFS gives you a yearly spending by category report in a few minutes. Put each transaction on its own row with date, category and amount, then total each category between two dates. Once you have the year, split it into quarters to plan around bills that arrive every three months.
How should the sheet be laid out?
Use three columns on a sheet called Data: date in column A, category in B and amount in C, with one expense per row from row 2. Put your category names in column E of a sheet called Report, and the start and end dates of your twelve months in H1 and H2.
Which formula gives each category's yearly total?
Place this in F2 of the Report sheet and fill it down. The ranges are fixed with dollar signs so they do not slide when you copy the formula.
- Yearly total for one category: =SUMIFS(Data!$C$2:$C$1000, Data!$B$2:$B$1000, E2, Data!$A$2:$A$1000, ">="&$H$1, Data!$A$2:$A$1000, "<="&$H$2)
- Monthly average: =F2/12
- Grand total of the report: =SUM(F2:F12)
- Check against the data: =SUMIFS(Data!$C$2:$C$1000, Data!$A$2:$A$1000, ">="&$H$1, Data!$A$2:$A$1000, "<="&$H$2)
Argument order matters: SUMIFS takes the range to add first, then each criteria range followed by its criterion. The same formulas work in both Excel and Google Sheets. If the check formula and the grand total disagree, some rows have a category spelled differently from the list in column E.
What does a quarterly plan look like?
Say the yearly totals are A$9,600 for groceries, A$2,400 for utilities, A$1,800 for fuel and A$3,000 for everything else. Add them and you get A$16,800 for the year. Divide by four and the quarterly plan is A$4,200. Divide by twelve and the monthly plan is A$1,400.
| Category | Year | Per quarter | Per month |
|---|---|---|---|
| Groceries | A$9,600 | A$2,400 | A$800 |
| Utilities | A$2,400 | A$600 | A$200 |
| Fuel | A$1,800 | A$450 | A$150 |
| Everything else | A$3,000 | A$750 | A$250 |
| Total | A$16,800 | A$4,200 | A$1,400 |
Why plan by quarter as well as by month?
Utility bills often arrive quarterly. Monthly figures hide that, so a quarterly view tells you when the cash needs to be ready. To total one quarter, change H1 and H2 to the quarter's dates and the same formula answers it.
When does a sheet stop being enough?
Mainly when you are re-pasting statements every month and fixing category spellings by hand. SpendixAI is built for India and INR, so it is not meant for Australian accounts. It is simply an example of tooling that groups spend by category over any period through a Report builder, which is what the formulas above are doing by hand.
How can you check the sheet is right?
Do a quick sample check. Pick one category and filter the Data sheet to it by hand, then add the visible amounts. The figure should match the SUMIFS result for the same dates. Then do the same for a second category. If both match, the formulas are working; if one is off, look for a stray space after the category name, an amount stored as text, or a date typed as plain text so that the date conditions skip it.
- Amounts left-aligned usually means they are text, not numbers.
- Dates left-aligned usually means they are text too.
- Trailing spaces in the category name break an exact match.
- Rows below row 1000 are ignored by the ranges above, so extend them if your list grows.
Finish with a review
- Do Report totals match the check formula?
- Is every account in the Data sheet?
- Are repayments and own-account transfers excluded?
- Have you saved a copy of the year before overwriting it?
Useful ideas start with a clearer picture of your own spending.
Explore your reports


