+ Reply to Thread
Results 1 to 25 of 25

sum across multiple sheets

  1. #1
    Forum Contributor
    Join Date
    05-30-2015
    Location
    Dubai
    MS-Off Ver
    Excel 2016 Mac & PC & 365
    Posts
    633

    sum across multiple sheets

    Hi,

    I have 12 sheets Jan-Dec. In another sheet, sheet1 I want to sum C6:Z6 in all the sheets if the month is over.
    In sheet1 I have January as text in B3 and a formula in B4 that returns the previous month from today, September

    I am using =SUM(January:December!C6: Z6) to get the total of all sheet, but how do I get January to Septembers total

    Any help greatly appreciated

    Kevin

  2. #2
    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
    79,387

    Re: sum across multiple sheets

    Maybe this?

    =SUM(INDIRECT("January:"&B4&"!C6:Z6"))
    Last edited by AliGW; 10-17-2018 at 07:32 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.

  3. #3
    Forum Contributor
    Join Date
    05-30-2015
    Location
    Dubai
    MS-Off Ver
    Excel 2016 Mac & PC & 365
    Posts
    633

    Re: sum across multiple sheets

    Thanks, i am getting Ref error?

  4. #4
    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
    79,387

    Re: sum across multiple sheets

    Attach the workbook.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: sum across multiple sheets

    Hi Ali, I was working on this too, with little success. I tried
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    All return #REF! errors
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  6. #6
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: sum across multiple sheets

    TMS

    You cannot use INDIRECT with SUM in this kind of formula - don't ask why : INDIRECT has always been an odd son-of-a-gun, and I'm not sure that using SUM with a 3D style reference is a documented feature? It's just a clever hack that works.

    In this link, a greater mind than mine grapples with the subject, and provides some illumination : https://answers.microsoft.com/en-us/...3fe1e80e6?db=5

    HTH

    DominicB
    Please familiarise yourself with the rules before posting. You can find them here.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: sum across multiple sheets

    @dominicb: Thanks ... b@gg@r

    Over to the OP. At least we tried and maybe moved the issue on a little.

  8. #8
    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
    79,387

    Re: sum across multiple sheets

    Yes, indeed - buggeration! Thanks for the head's up.

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: sum across multiple sheets

    I suppose another (simpler) way is to have a blank sheet called END, and then you can manually move the position of END depending on which sheets you want to include in the sum function. Then you can just use the formula:

    =SUM(January:END!C6:Z6)

    where END will be positioned between the September and October sheets. Next month just move the END sheet to the right (or the October sheet to the left).

    Hope this helps.

    Pete

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: sum across multiple sheets

    @Pete: Good plan ... not as aesthetically pleasing as using INDIRECT might have been but it should work nicely

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sum across multiple sheets

    I have been playing with the sumproduct sumif for a while. I still can't get it to accept a cell reference or a dynamic named range that selects a January:chosen end month.

    Buggeration again.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: sum across multiple sheets

    @Trevor,

    It also has the advantage of (working), and it avoids use of a volatile function.

    Pete

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sum across multiple sheets

    What a complete moron. It wasn't working 'cos I didn't spot the " " round my Named Range.

    Happy to explain. Each included sheet contributes 4 to the total...
    Attached Files Attached Files

  14. #14
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: sum across multiple sheets

    Nice one, Glenn. I'll give you some rep for that.

    Pete

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sum across multiple sheets

    Cheers! Those pesky wee " are hard to spot when you're sitting outside in very strong sunshine...

  16. #16
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: sum across multiple sheets

    Sitting outside in the sunshine looking at spreadsheets? ... you sad man

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sum across multiple sheets

    Sadder than you could possibly imagine....

  18. #18
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: sum across multiple sheets

    Well, I'm off to Spain tomorrow, but I probably won't be posting very much in the next two weeks!

    Pete

  19. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sum across multiple sheets

    That's where I am right now.... Just S of Tarragona. I've been on the road for about 2 months. Back home in about 3 weeks.

  20. #20
    Forum Contributor
    Join Date
    05-30-2015
    Location
    Dubai
    MS-Off Ver
    Excel 2016 Mac & PC & 365
    Posts
    633

    Re: sum across multiple sheets

    Thank you to everyone who looked at my question.......I wrestled with it for a couple of hours before I posted on the forum, so I am slightly pleased that it was a challenge for some of you gurus

    Glenn can you explain how its working please

    Pete enjoy your holiday!

    Thanks again everyone

    Kevin

  21. #21
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sum across multiple sheets

    I set up 2 named ranges:

    =Summary!$G$1:$G$13

    to populate the data validation box in B2. It simply allows you to pick the end month.

    The other (End_Month):
    =Summary!$G$1:INDEX(Summary!$G:$G,MATCH(Summary!$B$2,Summary!$G:$G,0))

    selects thhe range from G1 (January) to whatever end point is chosen from the DV cell (B2).

    Then the formula...

    =SUMPRODUCT(SUMIF(INDIRECT(End_Month&"!A1:A4")," < 1E100"))

    INDIRECT does not work with SUM in 3D (multi-sheet) references. Just one of thse things. Live with it!! SUMIF does. So a bit of sleight-of-hand is needed.

    SUMPRODUCT(SUMIF does work on 3D ranges... so this formula is looking at all sheets and summing ONLY those values that are less than 10 to the power of 100 (i.e. 1 followed by 100 zeros - a staggeringly large number that you will not normally encounter).

    Ta-Daa! Excel doesn't realise that it has been fooled and HAS done a SUM across multiple sheets while also using INDIRECT.

  22. #22
    Forum Contributor
    Join Date
    05-30-2015
    Location
    Dubai
    MS-Off Ver
    Excel 2016 Mac & PC & 365
    Posts
    633

    Re: sum across multiple sheets

    Great stuff! Thanks again Glenn

  23. #23
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sum across multiple sheets

    You're welcome and thanks for the Rep.

  24. #24
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: sum across multiple sheets

    @Glenn:
    That's where I am right now.... Just S of Tarragona.
    oh, just stop it ...

  25. #25
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sum across multiple sheets

    Motor Home life... Home is where I spend the night.

+ 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: 9
    Last Post: 10-16-2017, 03:25 AM
  2. copy and paste multiple sheets to multiple sheets
    By bjcowen9000 in forum Excel General
    Replies: 1
    Last Post: 07-11-2016, 08:57 PM
  3. Break multiple lists from different sheets in to new files with multiple sheets
    By belesky in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-01-2015, 12:43 PM
  4. Help indexing multiple sheets and matching multiple sheets
    By skell1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-11-2015, 11:16 PM
  5. Copy same column from multiple sheets to new wkbk with multiple sheets
    By rmcclendon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-08-2012, 09:17 AM
  6. Merge multiple excels with multiple sheets in a master excel with multiple sheets
    By marchi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-15-2012, 10:37 AM
  7. Need to Search for Value Across Multiple Sheets, Value Occur Multiple Times on Sheets
    By davidbriansmith in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-23-2011, 12:31 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