+ Reply to Thread
Results 1 to 11 of 11

Using Indirect function to sum across multiple sheets

  1. #1
    Registered User
    Join Date
    01-19-2009
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2007
    Posts
    6

    Using Indirect function to sum across multiple sheets

    My sheets are all categorized by date, and I want to be sum individual cells on each worksheet on one final summary worksheet. For example, if cell A1 on each sheet was units sold and I wanted to see how many total units were sold between Jan-04-08 and Jan-16-08 my formula on the summary sheet would be:

    =SUM('Jan-04-08:Jan-16-08'!A1)

    But I want to be able to easily modify what dates my summary sheet shows so I tried using the function:

    =SUM(INDIRECT("'"&N14&":"&N15&"'!A1"))

    where N14 was the starting sheet and N15 was the ending sheet. It keeps giving me #REF! however and I can't figure out why and don't know if this is something I can fix. Any help would be greatly appreciated.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    Try:

    =SUMPRODUCT(SUMIF(INDIRECT("'"&N14:N15&"'!A1"),"<>0"))

  3. #3
    Registered User
    Join Date
    01-19-2009
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2007
    Posts
    6
    Worked great thanks so much!

  4. #4
    Registered User
    Join Date
    01-19-2009
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2007
    Posts
    6
    Quote Originally Posted by DonkeyOte View Post
    Try:

    =SUMPRODUCT(SUMIF(INDIRECT("'"&N14:N15&"'!A1"),"<>0"))
    I used this formula and it worked great summing across two different worksheets, but when I tried to get it to sum across 3 it wouldn't work.

    I've got 3 tabs at the moment:
    (in order)
    Jan16
    Jan17
    Jan18

    I originally had N14 = Jan16 and N15= Jan17 and I got the correct sum. However, when I changed N15 = Jan18 it just added up the Jan16 and the Jan18 tab leaving out Jan17. I figured since Jan17 was sandwiched between the other tabs that its cell would get added as well.... is there anything I'm doing wrong or need to add/fix? Thanks sooo much.

    Dan

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    No, I', afraid it doesn't work like that -- you will need to list all the sheets to be included in a list and refer to that list in that said list.

    You may find creating a dynamic named range to hold the sheet listing worthwhile http://contextures.com/xlNames01.html -- this way you can add/remove sheets from the listing without needing to alter your formula... you can of course use some logic to build the list for you if your sheet naming convention is standard (based on a start/end date).

    There are other options open to you but they would require either

    a) manual intervention
    b) VBA
    c) 3rd party add-in

    Let us know which way you want to go.

  6. #6
    Registered User
    Join Date
    01-19-2009
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2007
    Posts
    6
    All the sheets in my worksheet are named after a date, with the exception of the last sheet which is the summary sheet. Ideally on the summary sheet I'd be able to total cells across a set start date/end date to see if during certain months my totals were higher than others. If that would be too hard though I'd be fine with the summary sheet just totaling all the sheets together without doing a specific date range.

    If I create a dynamic range I would list all the sheets I wished to sum and then create a dynamic range calling the list something easy like "DateList" correct? I'm not sure what my next step would be or how I could use logic to make building the list easier though.

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    I'm afraid I won't be able to provide an example until tomorrow as it's evening here in the UK ... if no one else puts up a sample I will do in the morning.... but yes in short your named range should contain a listing of the sheets to be included in the SUM -- each sheet must be listed - if using a dynamic named range (using COUNTA approach) make sure there are no blanks interspersed between the sheet references (ie consecutive rows).

  8. #8
    Registered User
    Join Date
    01-19-2009
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2007
    Posts
    6
    ok great... i really appreciate your help. right now i figure i'll just list all my sheets in one column and in the column next to it i'll create a dynamic range so that any sheets I wish to sum I can just copy/paste from the list of all sheets over to the dynamic range column. i'm guessing i have to use the indirect function somehow with the name of my dynamic range in it? not sure how that works though... any help in the morning would be great.

  9. #9
    Forum Contributor
    Join Date
    10-11-2007
    Location
    Sweden
    MS-Off Ver
    365
    Posts
    251
    If you have many sheets you can use this macro to retrieve all the sheet names.

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    I have as promised attached a file to illustrate how you can sum conditionally across sheets which I hope you will find useful... there are some pointers on the Summary sheet for your benefit in an attempt to outline how things work.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    02-14-2018
    Location
    Southern California
    MS-Off Ver
    2010
    Posts
    1

    Re: Using Indirect function to sum across multiple sheets

    Thank you! Your example gave me the tools to complete my project.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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