Create a Rolling Total in Excel

It’s easy to create a running total in Excel, and show the accumulated amount in each row. You can use the SUMIF function in a table, or show running totals in a pivot table.

But what if you want to show the total for a specific number of previous months – not all the previous months?

Total for Previous 12 Months

Recently, someone asked how to show a running total for the previous 12 months, instead of an ongoing running total.

Sometimes I include a Rolling Average in a client’s worksheet – usually a 3 month average. So, if we’re summing the amounts, I guess we can call that a Rolling Total (an accountant might have a different name for it).

Set Up the Data

To create a running 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

• The month start dates are in column A
• The monthly amounts are in column B
• The list is sorted by date

Formula for Rolling Total

In this example only the rows for the previous 11 months, and the current month, will be included in the total. The formula is explained in the next section.

1. Select the first cell in which you want to see the rolling total — cell C2 in this example
2. Enter the following formula, and press Enter:
   =SUMIF(A$2:A2,”>=” & DATE(YEAR(A2), MONTH(A2)-11, DAY(A2)), B$2:B2)
3. Copy the formula down to the last row with data.
4. Each row shows the Rolling Total for the latest 12 months (if available)

NOTE: If your list has month END dates, use this formula instead: =SUMIF(A$2:A2,”>=” & EOMONTH(A2,-$E$1), B$2:B2)

How It Works

Here is the formula again, that was entered in cell C2:

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

1. 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. The DATE function calculates the date that is 11 months prior to date in current row

     DATE(YEAR(A2),MONTH(A2)-11,DAY(A2))

3. The >= operator checks for dates that are greater than or equal to that date,

     “>=” & DATE(YEAR(A2),MONTH(A2)-11,DAY(A2))

4. For rows that meet the criterion, the formula sums the amounts in column B, starting in row 2 (B$2) down to the current row (B2)

     B$2:B2

Change the Number of Months

Instead of hard-coding the number of months in the formula, you could put that number in a cell, so it can be changed easily.

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

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

The heading in cell C1 also references the number of months:

  =E1 & ” Mth Total”

Download the Sample File

You can download the sample file from my website, on the Running Total and Rolling Total page.

The zipped Excel file is in xlsx format, and doesn’t contain any macros.

________________