Spreadsheet Practice

How to calculate a rolling 12-month total in Excel

Formula (Excel and Google Sheets)

=SUM(B2:B13)

Sum the current month and the 11 before it, with a relative range: =SUM(B2:B13) in the 12th month, filled down.

Result on the example
one result per row in C13, filled down to C16
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in C13, filled down to C16. Change any input and the result updates, or edit the formula to experiment.

How it works

With no $ signs, both ends of B2:B13 move down one row as you fill, so the window slides: row 14 adds February 2025 to February 2026, dropping the oldest month and adding the newest. The first full window ends in the 12th month, which is why the formula starts in row 13.

=SUM(B2:B13)

Result119830

  1. B2:B1312 rows × 1 col: $8,420, $9,150, $8,875, $9,610, …

    A range: 12 cells in one column.

  2. SUM(B2:B13)119830

    SUM: Adds numbers and ranges. Skips text and blanks.

    • number1B2:B1312 rows × 1 col: $8,420, $9,150, $8,875, $9,610, …

In Google Sheets

The same formula works in Google Sheets: =SUM(B2:B13). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SUMIFS($B$2:$B$16,$A$2:$A$16,">"&EDATE(A13,-12),$A$2:$A$16,"<="&A13)$119,830, $120,425, $121,115…Uses the dates instead of row positions, so a missing month doesn't shift the window.

Common mistakes

  • Locking the start with $B$2 turns it into a running total that never drops old months.
  • If a month is missing from the list, the row-based window covers 13 calendar months. The SUMIFS version doesn't have that problem.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate a rolling 12-month total

In C13, total the 12 months ending with January 2026, then fill down to C16 so each row shows the 12 months ending that month.

Questions

How do I calculate a rolling 3-month total?

Start in the third month with =SUM(B2:B4) and fill down.

How do I get a rolling 12-month average?

Use AVERAGE instead: =AVERAGE(B2:B13), filled down.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.