+ Reply to Thread
Results 1 to 9 of 9

Rolling 12 month total over multiple worksheets and multiple sum columns

Hybrid View

Hello.world Rolling 12 month total over... 01-28-2020, 09:56 PM
AliGW Re: Rolling 12 month total... 01-29-2020, 02:06 AM
Hello.world Re: Rolling 12 month total... 01-29-2020, 02:13 AM
AliGW Re: Rolling 12 month total... 01-29-2020, 02:19 AM
Hello.world Re: Rolling 12 month total... 01-29-2020, 02:28 AM
JeteMc Re: Rolling 12 month total... 01-30-2020, 07:15 PM
Hello.world Re: Rolling 12 month total... 02-01-2020, 12:42 AM
Pepe Le Mokko Re: Rolling 12 month total... 02-01-2020, 04:29 AM
JeteMc Re: Rolling 12 month total... 02-01-2020, 10:07 AM
  1. #1
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,827

    Re: Rolling 12 month total over multiple worksheets and multiple sum columns

    This somewhat assumes that the same number of rows will be used on the 2020 sheet as were used on the 2019 sheet. The following formulas may be pasted onto the Summary sheet.
    For cell A4: =SUM(SUMPRODUCT(('2019'!$C4:$C117>=EDATE(TODAY(),-12))*('2019'!$M4:$Z117)),SUMPRODUCT(('2020'!$C4:$C117>=EDATE(TODAY(),-12))*('2020'!$M4:$Z117)))
    For cell B4: =SUM(SUMPRODUCT(('2019'!$C4:$C117>=EDATE(TODAY(),-6))*('2019'!$M4:$Z117)),SUMPRODUCT(('2020'!$C4:$C117>=EDATE(TODAY(),-6))*('2020'!$M4:$Z117)))
    For cell C4: =SUM(SUMPRODUCT(('2019'!$C4:$C117>=EDATE(TODAY(),-3))*('2019'!$M4:$Z117)),SUMPRODUCT(('2020'!$C4:$C117>=EDATE(TODAY(),-3))*('2020'!$M4:$Z117)))
    For cell D4: =SUM(SUMPRODUCT(('2019'!$C4:$C117>=EDATE(TODAY(),-1))*('2019'!$M4:$Z117)),SUMPRODUCT(('2020'!$C4:$C117>=EDATE(TODAY(),-1))*('2020'!$M4:$Z117)))
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  2. #2
    Registered User
    Join Date
    01-28-2020
    Location
    Australia
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Rolling 12 month total over multiple worksheets and multiple sum columns

    Hi JeteMc,

    That is amazing! Thank you so much for that, I really appreciate it!

    May I ask, because I am trying to learn as much about excel as possible, how you got the sumproduct to work from M:Z? My previous attempts at doing so gave me some form of error. I'm just wondering what is different in this one?

    Thank you again for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 06-15-2017, 10:31 AM
  2. 30 Day Running or Rolling total (Multiple Sheets/Workbooks)
    By MuayThaiChick in forum Excel General
    Replies: 3
    Last Post: 02-18-2017, 05:31 PM
  3. [SOLVED] 12 month rolling total - Help!
    By Mummabee in forum Excel General
    Replies: 7
    Last Post: 11-25-2016, 10:18 AM
  4. Replies: 3
    Last Post: 07-16-2014, 02:53 PM
  5. Rolling subtraction of multiple cells in row until desired total reached
    By ducexcel2 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-22-2013, 10:29 AM
  6. Horizontal multi data 12 month rolling chart, multiple sheets.
    By allstar51788 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-29-2012, 08:53 AM
  7. Formula for Rolling Average from Multiple Worksheets
    By rstotty in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-21-2012, 01:16 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1