Excel Adjustable Rolling Totals
June 29, 2021
Thank you for reading the news, and you'll get the next email on July 13th.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
If you've got a list of monthly amounts, you can create a rolling total with the SUMIF function. For example, in each row, show a 3-month total -- the current month's amount, plus the previous 2 months.
To make it easy to adjust the formula, put the number of "rolling" months in a worksheet cell. That way, you just change one cell, instead of all the formulas. In this example, the cell is on the same sheet, but you could put it on a different sheet instead.
Here's the SUMIF and EOMONTH formula from cell C2, and it was copied down to the remaining rows:
How It Works
You can see more SUMIF tips and examples on the Excel Sum Cells page on my Contextures site.
Challenge: For something a little trickier, try the Rolling Total Challenge from the Jan 17 2017 newsletter. There are solutions in the sample file, and in the Jan 24 2017 newsletter. Did you find a different solution?
Here are some Excel-related links that you might find useful or interesting.
Power BI: On the Excel TV site, Jordan Goldmeier shows how to build your first Excel Power BI Dashboard Report, in a 33 minute video. There are sample files too, so you can follow along. (Level - Int/Adv)
Excel Battle: Chris Matyszczyk from ZD Net watched this month's online Excel battle, and it wasn't as exciting as Microsoft hoped! He knew the Canadian would win though. (Level - All)
Spreadsheets: Have you used any of these 8 Excel alternatives? I use Google Sheets occasionally, and tried Numbers briefly (hated it!), but I'm happy to stick with Excel. (Level - All)
Also see: Previous Newsletter Issues
We had a busy evening last Thursday! First, we were proud to watch our grandson's Grade 8 graduation online - he's headed to high school this fall, for a Cyber Arts program. After that, we enjoyed another Zoom beer and cheese tasting. We liked everything, except the cranberry and cinnamon coated cheese. Have you ever tried that strange flavour combination?
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2021/20210629ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: July 25, 2021 2:07 PM