Rolling 12-Month Total

Excel Formulas › Business

All versionsSUM

A rolling 12-month total (trailing twelve months, TTM) smooths out seasonality by always summing the last twelve months. As each new month arrives, the window slides forward automatically.

Quick formula: for monthly values in B, the rolling total at the current row:
=SUM(OFFSET(B2, 0, 0, -12, 1))
OFFSET grabs the 12-cell block ending at the current month; SUM totals it. Negative height counts upward in Excel.

Functions used (tap for the full reference guide):

SUMOFFSET

The example

Each month sums itself plus the prior 11.

ABC
1MonthSalesTTM
2Dec Y1$8,000$92,000
3Jan Y2$7,500$94,500

The formula

Sum the trailing twelve months:

=SUM(OFFSET(B13, 0, 0, -12, 1)) // this month + prior 11

How it works

A sliding 12-row window does the work:

  1. OFFSET(currentCell, 0, 0, -12, 1) defines a 12-row tall block ending at the current month (negative height counts upward).
  2. SUM totals that block — the trailing twelve months.
  3. Copy down; each row’s window slides forward by one month automatically.
  4. Only valid once you have 12 months of history; earlier rows would sum fewer than 12.

Prefer a sturdier formula? If your data has a date column, =SUMIFS(values, dates, ">"&EDATE(thisMonth,-12), dates, "<="&thisMonth) avoids OFFSET’s volatility and survives inserted rows.

Try it: interactive demo

Live demo

Enter monthly values (one per line); TTM = last 12.

Latest TTM:

Variations

SUMIFS by date (robust)

Date-driven window:

=SUMIFS(val, dt, ">"&EDATE(m,-12), dt, "<="&m)

Rolling 3-month

Change the height:

=SUM(OFFSET(cur,0,0,-3,1))

Rolling average

Divide by the window:

=SUM(OFFSET(cur,0,0,-12,1))/12

Pitfalls & errors

Needs 12 months first. Rows before the 12th sum a short window — blank them or wait until enough history exists.

OFFSET is volatile. It recalculates often and can be fragile with inserted rows; the SUMIFS-by-date form is sturdier for big models.

Gaps break the count. OFFSET counts rows, not months — missing months throw the window off. Keep one row per month.

Practice workbook

📊
Download the free Rolling 12-Month Total practice workbook
A rolling-total sheet with the OFFSET window, SUMIFS-by-date, 3-month, and rolling-average variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate a rolling 12-month total in Excel?
Use =SUM(OFFSET(currentCell, 0, 0, -12, 1)) to sum the trailing twelve rows, or the date-driven =SUMIFS(values, dates, ">"&EDATE(month,-12), dates, "<="&month).
Why is OFFSET risky for rolling totals?
OFFSET is volatile (recalculates frequently) and counts rows rather than months, so gaps or inserted rows can break it. SUMIFS by date is sturdier.
How do I make a rolling 3-month total instead?
Change the OFFSET height: =SUM(OFFSET(currentCell, 0, 0, -3, 1)).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Moving average · Sum by quarter · Running cash balance

Function references: SUM · OFFSET