+ Reply to Thread
Results 1 to 9 of 9

Rolling 12 month total over multiple worksheets and multiple sum columns

  1. 01-28-2020, 09:56 PM #1
    Hello.world
    Hello.world is offline
    Registered User
    Join Date
    01-28-2020
    Location
    Australia
    MS-Off Ver
    Office 365
    Posts
    4

    Post Rolling 12 month total over multiple worksheets and multiple sum columns

    Hi there!

    I have a pilot's logbook spreadsheet that I have made. The workbook is made up of multiple sheets, one per year (i.e. 2007, 2008, 2009, etc.) and then one summary sheet. On the summary sheet, I am trying to figure out how to make a rolling total of the flying hours of the last 12 months, 6 months, 3 months and 1 month. I can't figure out how to do it not only over multiple sheets but also over multiple columns in the sum range (I need to sum from M4:Z200). I would like the totals to automatically update every day as time progresses. The dates for each flight are contained within cell C, which is a date formatted to show as a day of the week.

    This is my first time using this forum, I apologise if there isn't enough detail, I will gladly add more if needed!

    Thank you in advance for your help.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-29-2020, 02:06 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,779

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

    Welcome to the forum.

    Which 'newest' version of Excel do you have? 2016? 2019? 365 subscription? Please update your profile accordingly. If you have a recent version, why is the workbook in .xls format and not .xlsx?

    Your summary sheet is blank: please mock up manually what you are hoping to see there and attach again.
    Last edited by AliGW; 01-29-2020 at 02:11 AM.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 01-29-2020, 02:13 AM #3
    Hello.world
    Hello.world is offline
    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 Ali,

    Apologies for that! It's an Office 365 subscription through a university. I have also reattached my spreadsheet, with the correct file format and information.

    Thank you!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 01-29-2020, 02:19 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,779

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

    Nearly there! You need to tell us the expected results (manually calculated) for the data as it currently stands. This is so that helpers can check that the solutions they offer are going to work for you.
    Reply With QuoteRegister To Reply
  5. 01-29-2020, 02:28 AM #5
    Hello.world
    Hello.world is offline
    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

    Third time is the charm! I'll get there eventually . Sorry about that.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 01-30-2020, 07:15 PM #6
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,552

    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.
    Reply With QuoteRegister To Reply
  7. 02-01-2020, 12:42 AM #7
    Hello.world
    Hello.world is offline
    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 With QuoteRegister To Reply
  8. 02-01-2020, 04:29 AM #8
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

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

    Which error did you get?
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  9. 02-01-2020, 10:07 AM #9
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,552

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

    You're Welcome and thank you for the feedback. I would need to see the formula that produced the error in order to help in that regard. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Rolling 12 month attendance occurrence across multiple worksheets within same workbook
    By larryvoorhees in forum Excel Formulas & Functions
    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. [SOLVED] Rolling 12 month total formula which will automatically change at new month
    By TotallyLost in forum Excel General
    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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1