Spreadsheets

Monthly summary sheet with SUMIFS: income, spend and savings

A monthly summary sheet template in Excel: months down, categories across, SUMIFS from a transaction table, plus income, net and savings rate columns.

Editorial illustration for Monthly summary sheet with SUMIFS: income, spend and savings

A monthly summary sheet needs one row per month, one column per category, and SUMIFS formulas that read from a single transaction table. Add columns for income, total spend, net and savings rate, and you have a view of the whole year on one screen. Below is the layout and the exact formulas.

What should the transaction table hold?

ColumnHeadingExample
ADate14-Apr-2025
BDescriptionElectricity bill
CCategoryUtilities
DTypeExpense or Income
EAmount2,350

Record every amount as a positive number and use the Type column to say whether it is income or spending. Name the sheet Txn, and keep one row per transaction with no blank rows or merged cells.

How is the summary sheet laid out?

  • Column A: the first day of each month, from 01-Apr-2025 to 01-Mar-2026, formatted mmm-yy.
  • Row 1, columns B onwards: category names exactly as they appear in the Txn Category column (Rent, Groceries, Utilities and so on).
  • Then columns for Income, Total spend, Net and Savings rate.

Which formulas go in each cell?

For a category cell in B2:

  • =SUMIFS(Txn!$E$2:$E$2000, Txn!$C$2:$C$2000, B$1, Txn!$D$2:$D$2000, "Expense", Txn!$A$2:$A$2000, ">="&$A2, Txn!$A$2:$A$2000, "<"&EDATE($A2,1))
  • Income (column H, say): =SUMIFS(Txn!$E$2:$E$2000, Txn!$D$2:$D$2000, "Income", Txn!$A$2:$A$2000, ">="&$A2, Txn!$A$2:$A$2000, "<"&EDATE($A2,1))
  • Total spend (column I): the same formula with "Expense" and no category condition. This catches expenses even if a category name is missing from the header.
  • Net (column J): =H2-I2
  • Savings rate (column K): =IF(H2=0,"",J2/H2)

Format K as a percentage. SUMIFS takes the sum range first, then pairs of range and criterion, which is why the amount column leads each formula.

Can you show a worked row?

ItemValue (₹)
Income for the month92,000
Total spend64,500
Net27,500
Savings rate29.9%

Net is 92,000 - 64,500 = 27,500, and 27,500 / 92,000 is about 0.299, shown as 29.9%. These figures are hypothetical.

How do I make sure no category is missing?

Add a Gap column: =I2-SUM(B2:G2), where B to G hold your category columns. It should read 0. If it shows a number, some spending sits in a category that has no column, or the category was spelled differently. Add an Other column, or correct the spelling in the Txn sheet. Check the total of the Gap column at the bottom of the sheet; it should be 0 for the whole year.

How do I handle budgets and the financial year?

Add a Budget row under the year's totals, one figure per category, and a Variance row equal to actual minus budget. With a hypothetical Groceries budget of ₹9,000 and actual spend of ₹9,800, the variance is +800, meaning ₹800 over. A conditional formatting rule that turns positive variances red makes overspending visible. Keep budgets as plain numbers you chose, and revise them when your circumstances change, such as a new rent.

For the financial year, start the list at 01-Apr and run twelve months to 01-Mar. Add a totals row with =SUM for each column, and a savings rate for the year computed from the totals, not by averaging the monthly percentages. Make a copy of the workbook for each financial year, so old totals stay frozen.

Checklist

  1. Enter transactions with real dates and positive amounts.
  2. Fill formulas right and down and check the Gap column.
  3. Compare each month's total spend with the statements.
  4. Save a copy before starting a new year.

When a sheet stops being enough: typing or pasting every transaction takes time. SpendixAI imports statements you upload, categorises them with your own rules first, and has overview reports, budgets and a Report builder, so a sheet like this remains useful for people who want full control and a tool for those who want less data entry.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports