Home > Formulas > Sum > Running Total

Excel Running Total Examples

Debra Dalgleish - Contextures

My examples show how you can create a rolling total or running total in Excel. A running total includes all rows down to the current row. A rolling total sums a specified range of items, such as a 7 day total, or 3 months.

Also see: Running Totals in Pivot Tables

rolling total compared to running total

1) Running Total or Rolling Total

A running total and a rolling total both calculate a cumulative sum for numbers in a list, down to the current row.

In the screen shot below, the two types of totals are shown side-by-side.

  • Running total includes all rows above and including the current row.
    • March running total includes all 9 months in the table.
  • Rolling total includes a specified range of items, including the current row.
    • October rolling total includes Aug, Sept and Oct, but not July.

There are examples for running and rolling totals in the sections below, and you can get the sample Excel file in the download section.

rolling total compared to running total

2) Running Total

To see a running total in each row of an Excel list, you can use the SUM function, with the starting row of the sum range locked as an absolute reference. There are slightly different steps below, based on the list location:

  1. for a running total in a worksheet list
  2. for a running total in a named Excel table

2.1) Running Total in Worksheet List

For a worksheet list (not a named Excel table), this video shows how to set up the running total formula, and lock the starting row. The written steps are below. For the video transcript, go to the Running Total Video page.

2.1.1) Running Total - Worksheet List

For a worksheet list (not a named Excel table), follow these steps to create a running total. In the screen shot below, amounts are entered in column C, and a running total is calculated in column D.

  • Enter this formula in cell D2
    • =SUM(C$2:C2)
  • Copy the formula down to the last row with data

See how this formula works, in the section below.

sum running total formula

2.1.2) How the Formula Works

The formula uses two types of cell references:

  • Absolute reference to row 2 as the starting point -- C$2
    • column is not locked (no $ before C)
    • row is locked ($2)
  • Relative reference to the ending point -- C2
    • no dollar signs to lock column or row

With these row references:

  • starting row will not change, when you copy the formula down to the rows below
  • ending row will adjust automatically, when you copy the formula down

Here is the formula in cell D6 -- the starting point has stayed the same and the ending point is in the current row -- C6

  • =SUM(C$2:C6)

how sum running total formula works

2.2) Running Total in Excel Table

For a named Excel table, we can't use the worksheet list formula in the previous section. First, I'll show you the problem with that formula, and then you'll see the formula that works in a named table.

2.2.1) Problem with Running Total

After you enter the formula in cell D2 of the named Excel table, it automatically fills down, and the running total looks correct.

running total in named Excel table

But, as soon as you start a new row at the bottom of the table, the formula in the last row changes.

Before the row was added, cell D6 had this formula:

  • = SUM(C$2:C6)

As soon as the next entry was started in row 7, the formula in D6 automatically changed. Now it has an incorrect ending reference to C7, instead of C6:

  • =SUM(C$2:C7)

incorrect running total in named Excel table

As each new row is added, the formulas in the bottom rows keep changing, to show the latest row number.

2.2.2) Running Total Formula for Named Table

To avoid that problem, I use a slightly different formula for a running total in a named Excel table.

In the screen shot below, amounts are entered in column C, and a running total is calculated in column D.

  • To start the formula in cell D2, type the following (don't click on cell C1:  =SUM(C$1:
  • Next, click on cell C2, and Excel will add a structured table reference: [@Amt]
  • Finally, add a closing bracket to the formula, then press the Enter key
    • =SUM(C$1:[@Amt])

The formula automatically fills down to cell D6

  • All cells in column D show the same formula
  • If you add a new row, none of the existing formulas will change.

running total formula with structured table reference

2.2.3) How the Formula Works

The formula uses an absolute reference to a heading cell as the starting point -- C$1

  • To prevent problems if a new data row is added at the top, the starting cell is in the table heading row
  • Because the heading cell contains text, its value is treated as zero, and won't affect the running total

For the ending point, there is a structured table reference -- [@Amt]

  • This is a reference to the Amt cell in the current row
  • With that table cell reference, there's no problem when new rows are added to the Excel table

This screen shot shows that when a new row is started, cell C6 continues to show its original formula, and the running total amounts are correct in each row

running total formula with structured table reference

3) Rolling Total - 12 Months

Instead of summing all the amounts, a rolling total sums a specific number of amounts, such as 3 months, or 12 months.

In this example, my formulas will calculate a rolling total for 12 months. Each sum will include:

  • amounts for the previous 11 months (if available)
  • amount for the current month

3.1) Rolling Total Data

To create a 12 month rolling total, I set up a small table with test data. There is only one entry per month, but the formula would work with multiple rows per month.

  • Month start dates are in column A
  • Month amounts are in column B
  • List is sorted by date (oldest to newest)

For the rolling total to calculate correctly, the data must be sorted by date, from oldest to newest.

rolling total data sorted by date

3,2) Rolling Total Formula

To calculate the rolling 12-month total, I entered the following formula in cell C2, and copied it down to the last row with data.

  • =SUMIFS(B$2:B2, A$2:A2,">=" & DATE(YEAR(A2), MONTH(A2)-11, DAY(A2)))

In column C, each row shows the Rolling Total, for the latest 12 months (or fewer, if 12 months aren't listed)

12-month rolling total

NOTE: If your list has month END dates, use this formula instead:

  • =SUMIFS(B$2:B2, A$2:A2,">=" & EOMONTH(A2,-$E$1))

You can see more EOMONTH examples on the Excel Date Functions page.

3.2.1) How It Works

Here is the rolling total formula from cell C2, with colour coding added:

  • =SUMIF(A$2:A2, ">=" & DATE(YEAR(A2), MONTH(A2)-11, DAY(A2)),B$2:B2)

And here's how the 12-month rolling total formula works:

  1. First, the formula checks the dates in column A, starting in row 2 (A$2), and down to the current row (A2)
    • =SUMIF(A$2:A2,
  2. Next, the DATE function calculates the date that is 11 months before the date in the current row. The YEAR, MONTH and DAY functions get numbers from the date in cell A1.
    • DATE(YEAR(A2), MONTH(A2)-11, DAY(A2))
  3. Next, the >= operator checks for dates that are greater than or equal to that date,
    • ">=" & DATE(YEAR(A2),MONTH(A2)-11,DAY(A2))
  4. Finally, for rows that meet the date criterion, the formula sums the amounts from column B, starting in row 2 (B$2), down to the current row (B2): B$2:B2

4) Rolling Total - Variable Months

Instead of always seeing a 12-month rolling total, you might want to see different scenarios, in some workbooks.

To do that, you could put the "months" number in a worksheet cell, so it can be changed easily.

In the screen shot below, the number of months is entered in cell E1, and the formula will include a reference to that cell.

rolling total with variable number of months

4.1) Variable Month Formula - All Rows

In the screen shot below, the number of months is entered in cell E1.

Old Formula: Here is the old formula in cell C2, which has the number of previous months (11) hard coded into the MONTH calculation

  • =SUMIF(A$2:A2, ">=" & DATE(YEAR(A2), MONTH(A2)-11, DAY(A2)),B$2:B2)

New Formula: In the formula below, I replaced the "11", with an absolute reference to cell $E$1., and copied it down to the last row of data

  • =SUMIF(A$2:A2, ">=" & DATE(YEAR(A2), MONTH(A2)-$E$1, DAY(A2)),B$2:B2)

Note: With this formula, the rolling total shows in every row, even if:

  • there are no previous months,
  • there are fewer than the specified number of months

rolling total in every row

4.2) Variable Month Formula - Valid Rows

In some workbooks, you might want to see the rolling total, only in rows where there are the required number of previous months.

To do that, use the following formula in cell C2, and copy it down to the last row of data.

  • =SUMIF(A$2:A2, ">=" & DATE(YEAR(A2), MONTH(A2)-$E$1, DAY(A2)),B$2:B2)

Note: With this formula, the result is a number or empty string:

  • if the variable number of months are available, for the current row, the rolling total appears
  • if not, the formula result is an empty string

rolling total in valid rows only

5) Get the Sample File

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Tutorials

Pivot Table Running Totals

Excel Date Functions

SUMIFS Function Examples

Excel Sum Functions

 

 

Last updated: May 25, 2025 3:27 PM