How do I? Using INDIRECT to SUM Range of Numbers on Different Worksheet

[ivansmashem]

Hi, Everyone. First time posting here because it's the first time in a while I've been stumped by something in Excel! Here is what I am trying to do (and you can PM me for full file where I completely lay out all formulas):

Background: I am using Excel 2013 on Windows 7, 64-bit.

I am trying to sum a range of numbers, in this example B2:D9, that are on a different worksheet by using the SUM() and INDIRECT() functions. So the easiest way to do this would be to just directly use only the SUM() function as shown here:

=SUM(Sheet2!$B$2:Sheet2!$D$9)

Note: Sheet1 is my current sheet on which I am entering this formula, and Sheet2 refers to the "different" sheet whose numbers I am trying to sum.

Using the code above, I have no trouble summing the numbers from a separate sheet, so I wouldn't expect the issue to be here. My next test is to make sure that INDIRECT() can be used with SUM(), as is shown here:

=SUM(INDIRECT("B"&$F$2&":"&"D"&$G$2))

Note: F2 contains the Row number in Column B where I start summing numbers. G2 contains the Row number in Column D where I stop summing numbers.

The above also works with no problem. However, there seems to be an issue with the INDIRECT() function trying to access other sheets. I can confirm that this does normally work as long you don't have INDIRECT() nested in another function, such as SUM(), like I do above. However, even though both of the above work fine, the following does not:

=SUM(INDIRECT("'Sheet2'!"&"B"&$F$2&":"&"'Sheet2'!"&"D"&$G$2))

Instead, this returns simply "#REF!" instead of the sum of the numbers on the second sheet.

Does anyone know why this does not work? Am I trying to do something incorrectly? If so, how should I go about doing this? The purpose for me trying to do it this way is that I need to interact with a range of data from a separate sheet that is dynamically determined when I run the formula. My actual project involves me using INDIRECT() as an input to the INDEX() function, but I have confirmed that the INDEX() function is working as it should, and that my issue is with INDIRECT(), and using SUM() is a much easier way of testing what is going on.

Thank you all very much for having taken the time out to read my first pickle of a situation! I very much appreciate the help, and I look forward to seeing your brilliant responses soon!

Best,
Josh

 

[Webbarr]

Hi Josh,

I am also using 2013 & have just tested the Indirect nested in a sum function across two worksheets.

I have data in column D in ws1 & data in column E of ws2. I am getting the correct results using this formula -

=SUM(INDIRECT("D" &Sheet3!E1& ":D" &Sheet3!E2))

 

[Jonmo1]

You don't have to specify the sheet on BOTH cell referenes of the range
=SUM(Sheet2!$B$2:Sheet2!$D$9)
Should be just
=SUM(Sheet2!$B$2:$D$9)

So your indirect would be
=SUM(INDIRECT("'Sheet2'!"&"B"&$F$2&":"&"D"&$G$2))


Standard formula (without indirect) allows (but does not require) you to specify the sheet on both parts.
But indirect doesn't like it.
I'm not sure why though.

 

[Jonmo1]

Also, the : and the D do not need to be concatenated seperately

=SUM(INDIRECT("'Sheet2'!"&"B"&$F$2&":"&"D"&$G$2))
Would just just be
=SUM(INDIRECT("'Sheet2'!"&"B"&$F$2&":D"&$G$2))

 

[ivansmashem]

Hi, Guys!

Thank you both very much for your answers! My Excel sheet is now working, and I can move on to getting my lookup table created using INDEX().

It seems like INDIRECT() only doesn't like the double 'Sheet' mention when you include it in quotes, so Webbarr's method works slightly differently, but still works.

And Jonmo1, that's actually how I had initially written it, but I segregated everything when I had to do multiple edits to make it easier on my eyes!

Again, thank you both very much for your answers. No doubt, I would have just been going blind staring at the problem until I gave it a completely fresh go next week otherwise :/

 

[Jonmo1]

Glad to help..

I can't get it to work at all with the sheetname on both parts of the range reference.
With or without the ' for sheetnames with spaces.

=SUM(Sheet2!B2:Sheet2!D10) - works
=SUM(INDIRECT("Sheet2!B"&F2&":D"&G2)) - works
=SUM(INDIRECT("Sheet2!B"&F2&":Sheet2!D"&G2)) - #REF!