# Using Indirect function to sum across multiple sheets

1. ## 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. Try:

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

3. Worked great thanks so much!

4. Originally Posted by DonkeyOte
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. 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. 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. 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. 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. If you have many sheets you can use this macro to retrieve all the sheet names.

10. 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.

11. ## Re: Using Indirect function to sum across multiple sheets

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

##### Users Browsing this Thread

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

#### 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