Spreadsheets

Rolling 12-month spending total in Excel with SUMIFS and EDATE

Build a rolling 12-month spending total in Excel with SUMIFS and EDATE, with a worked example and how it differs from financial-year-to-date.

Editorial illustration for Rolling 12-month spending total in Excel with SUMIFS and EDATE

To total the last 12 full months in Excel, build a start date and an end date from today's date and pass both to SUMIFS. With dates in column A and amounts in column C, the start is the first day of the month twelve months back and the end is the first day of the current month, so the result always covers the 12 complete months ending last month.

The formula, built in three cells

Put these in E1 to E3 on the same sheet as your data, or on a summary sheet and adjust the references:

  • E1 (start): =EDATE(DATE(YEAR(TODAY()),MONTH(TODAY()),1),-12)
  • E2 (end, not included): =DATE(YEAR(TODAY()),MONTH(TODAY()),1)
  • E3 (rolling total): =SUMIFS($C$2:$C$500,$A$2:$A$500,">="&$E$1,$A$2:$A$500,"<"&$E$2)

The end condition uses "<" with the first day of this month rather than "<=" with last month's final day. A date cell that carries a time of day, say 31 October at 6 pm, is later than midnight on 31 October and would be missed by the second version.

If you prefer EOMONTH, the same start is =EOMONTH(TODAY(),-13)+1 and the same end is =EOMONTH(TODAY(),-1)+1. Both pairs give identical dates.

What does it add up to in a worked example?

Say today is 12 November 2026. The start is 1 November 2025 and the end is 1 November 2026, which is not included, so the window is November 2025 to October 2026. Suppose your monthly totals, in made-up figures, are these:

MonthSpent (₹)
Nov 202538,000
Dec 202546,000
Jan 202641,000
Feb 202637,000
Mar 202652,000
Apr 202639,000
May 202644,000
Jun 202640,000
Jul 202642,000
Aug 202645,000
Sep 202651,000
Oct 202655,000

Those twelve figures add up to ₹5,30,000, so the rolling total is ₹5,30,000 and the monthly average is 5,30,000 divided by 12, about ₹44,167. The twelve days of November so far are not in it, which is the point: a half-finished month never drags the figure down.

How is this different from year-to-date?

Year-to-date answers "how much since the financial year began?". A rolling total answers "how much over the last twelve months?" whatever the calendar says.

MeasureStartsEndsLength of window
Rolling 12 months1 Nov 202531 Oct 2026Always 12 full months
Financial year to date1 Apr 2026Today, 12 Nov 2026Grows from nothing in April to a full year in March

For the April to March year, the start date is =DATE(YEAR(TODAY())-(MONTH(TODAY())<4),4,1). In the example, April to October adds up to ₹3,16,000 before November's spending is counted. Rolling figures are better for comparing one stretch of the year with another; year-to-date is better for planning against a financial-year budget.

How do I get the rolling total for one category?

Add a category pair to SUMIFS. With the category names in column B and the one you want typed in G1:

  • =SUMIFS($C$2:$C$500,$B$2:$B$500,$G$1,$A$2:$A$500,">="&$E$1,$A$2:$A$500,"<"&$E$2)

Change G1 from "Groceries" to "Fuel" and the answer follows. A small block listing every category next to this formula gives you a rolling total per category, and a sum of that block should match E3.

What quietly breaks this formula?

  • Ranges that stop at row 500 when your data runs to row 800. Extend the ranges, or turn the data into a Table.
  • Refunds entered as positive numbers. Decide whether a refund is a negative expense and keep to it.
  • TODAY recalculates whenever the file opens, so a figure you printed on 28 November will change on 1 December. Paste values into a log sheet at each month end if you want a record that stays put.

Rules and limits for anything tax-related change from year to year, so use a rolling total as a spending record and confirm tax questions with your CA. When pasting statements into the sheet every month starts to wear you down, SpendixAI can import them and give you the same kind of totals through its reports.

SPENDIXAI JOURNAL

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

Explore your reports