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.
- Select the first cell in which you want to see the rolling total — cell C2 in this example
- Enter the following formula, and press Enter:
=SUMIF(A$2:A2,”>=” & DATE(YEAR(A2),MONTH(A2)-11,DAY(A2)),B$2:B2) - Copy the formula down to the last row with data.
- 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 Sum Functions page. That file has 3 Rolling Total examples – the 3rd one hides the total if the number of previous months is less than the number entered in cell E1.
________________
This was awesome – super helpful. Thank you!
IM HAVING A SMALL ISSUE WITH THIS. I HAVE INSERTED THE END CELL WHICH HAS NO VALUES INSERTED YET. FOR EXAMPLE Z1145:Z10000. SO FAR THERE IS ONLY DATA UP TO Z1700. THE FORMULA RETURNS A ZERO. IS THERE A WAY TO HAVE IT ADD UP WHAT I HAVE INSERTED ALREADY??
Ryan.
Nobody likes to read capitalized words.
NOBODY.
No one, unless you are purposely creating a double negative and saying everyone likes to read capitalized words!
This is SO true. With cap words, you are already dead to me 🙁
Typical Ryan
ok, forget the caps, can “nobody” answer Ryan’s question
Is there a way to adjust this forumla to exclude the current month?
I would like to use this so is just shows the 3 months.
Thanks
I found this when I was looking for something else. I have an app that measures a daily score, but it only presents me with a rolling 7 day running total. So on Day 8 I get the sum of Days 1 – 7, Day 9 the sum of Days 2 – 8 etc.
Is there a way of extracting the daily scores from the daily running total.
Thank you if you are able to assist.
John, do you have that 7-day running total formula? Thats exactly what I am looking for but can’t figure out the syntax.
This formula doesn’t seem to work when the dates are the last day of the month rather than the first day of the month.
Clay, for month end dates, use this formula instead:
=SUMIF(A$2:A2,”>=” &EOMONTH(A2,-$E$1), B$2:B2)
Debra, I’m trying to do this but as row, column not column, row (my dates are in the column, not by row). I tried reversing the formula but it’s not working. John’s comment (above) says he has a formula the gives him a running 7-day total. That’s what I need but can’t find one. When I open the spreadsheet, I need the formula to find the column that is Today and then add the previous 6 days (by row). I have a screenshot of what I’m trying to do but can’t post it. Thanks.
Do you have a similar formula please where
1. Column A is a series of dates to create a rolling 12 month total for any dates within the previous 12 month period
2. Column A is a series of dates to create a rolling 52 week total for any dates that fall within that previous 52 week period
Thanks