Contextures

Contextures News 2021-06-29

Excel Adjustable Rolling Totals

June 29, 2021

Power BI dashboard, rolling totals, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

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.

Rolling Totals

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:

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

rolling total formula

How It Works

  • First, the EOMONTH function finds the end date from 2 months ago. In row 9, the month is June, so the "2 months ago" date is April 30th.
    • EOMONTH(A2,-($E$1-1))
  • Next, the SUMIF function totals:
    • the values in column B, from B2, down to the current row
    • IF the date in column A is greater than or equal to that EOMONTH result
      • =SUMIF(A$2:A2,">=" & April 30, B$2:B2)

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?

rolling total challenge

Excel Articles

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

Strange Combo

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?

weekly photo

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.

Debra Dalgleish
dsd @contextures.com
ctxdebra @gmail.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

contextures newsletter info

 

Last updated: July 25, 2021 2:07 PM