INDIRECT SUM WITH SHEET NAMES

[occy]

I am trying to sum a particular cell across a range of worksheets. My formula is:

=SUM(INDIRECT(K4&":"&K5&"!"&"J60"))

Where K4 contains the name of the first worksheet and K5 contains the name of the last worksheet in the range I want to sum. J60 is the cell I want to sum.

I get the dreaded #ref! result. Help would be much appreciated.

Thanks

 

[asusetyo]

No, i use the same example on page 1, Hoja sheets, with the same formula, but the formula pasted in cell A1, not in B7

 

[Aladin Akyurek]

No, i use the same example on page 1, Hoja sheets, with the same formula, but the formula pasted in cell A1, not in B7

Here is the workbook that implements Juan's post and mine:
https://dl.dropboxusercontent.com/u/65698317/Juan%20Pablo%20Gonz%C3%A1lez%20%2B%20Aladin%20Akyurek%20across%20sheets%20calc.xlsm

 

[Levi B]

I just came across this thread as I am working on a similar problem. In my case I have a list of the sheet names in cells A1:A10, and I want to sum cell C1 of the third to seventh sheets, so I have the number 3 (for the third sheet) in B1 and the number 7 (for the seventh sheet) in B2. In this simplified case, the following formula works:

=SUMPRODUCT(SUM(INDIRECT("'"&INDEX($A$1:$A$10,B1):INDEX($A$1:$A$10,B2)&"'!C1")))

Strangely wrapping everything in SUMPRODUCT is working but leaving that out and using an array formula is not. For some reason with SUMPRODUCT the two indexes return A3:A7 then it splits into an array of {[A3];[A4];[A5];[A6];[A7]}, while with the array formula the two indexes return A3:A7 then it takes A3.

For clarity here's the array formula that doesn't work:

{=SUM(INDIRECT("'"&INDEX($A$1:$A$10,B1):INDEX($A$1:$A$10,B2)&"'!C1"))}

 

[Aladin Akyurek]

I just came across this thread as I am working on a similar problem. In my case I have a list of the sheet names in cells A1:A10, and I want to sum cell C1 of the third to seventh sheets, so I have the number 3 (for the third sheet) in B1 and the number 7 (for the seventh sheet) in B2. In this simplified case, the following formula works:

=SUMPRODUCT(SUM(INDIRECT("'"&INDEX($A$1:$A$10,B1):INDEX($A$1:$A$10,B2)&"'!C1")))

Strangely wrapping everything in SUMPRODUCT is working but leaving that out and using an array formula is not. For some reason with SUMPRODUCT the two indexes return A3:A7 then it splits into an array of {[A3];[A4];[A5];[A6];[A7]}, while with the array formula the two indexes return A3:A7 then it takes A3.

For clarity here's the array formula that doesn't work:

{=SUM(INDIRECT("'"&INDEX($A$1:$A$10,B1):INDEX($A$1:$A$10,B2)&"'!C1"))}

We need here a second round of evaluation to pick out the values INDIRECT delivers.

1a) =SUMPRODUCT(SUM(INDIRECT(...)))

SUM is the first round, SUMPRODUCT the second.

1b) =SUMPRODUCT(SUBTOTAL(9,INDIRECT(...)))

1c) =SUMPRODUCT(N(INDIRECT(...)))

SUBTOTAL and N calls effects the first round evaluation.

2a) {=SUM(SUBTOTAL(9,INDIRECT(...)))}

2b) {=SUM(N(INDIRECT(...)))}

The latter two will behave the same as the first versions.

For more, do a google search on "dereferencing" (or deferencing with the typo) and/or "second round of evaluation".

 

[Levi B]

Thank you Aladin!

 

[Aladin Akyurek]

Thank you Aladin!

You are welcome.

 

[bshaggy6]

Hello,

I think my problem is similar to what is being talked about in this thread. I'm trying to sum values in cell A3 on worksheets labeled from 1 to 31 (representing days in the month) and have it summed in cell A7 on a worksheet labeled "Weekly Report Results". In cell g12 and h12 of the weekly report worksheet, i want to be able to put numbers between 1 and 31 and have it sum the values in A3 of those specific worksheets i put in cell g12 and h12. Can anyone help me out? thank you in advance!

 

[Levi B]

Hello,

I think my problem is similar to what is being talked about in this thread. I'm trying to sum values in cell A3 on worksheets labeled from 1 to 31 (representing days in the month) and have it summed in cell A7 on a worksheet labeled "Weekly Report Results". In cell g12 and h12 of the weekly report worksheet, i want to be able to put numbers between 1 and 31 and have it sum the values in A3 of those specific worksheets i put in cell g12 and h12. Can anyone help me out? thank you in advance!

It sounds like you're looking for:

=INDIRECT("'"&G12&"'!A3")+INDIRECT("'"&H12&"'!A3")

 

[bshaggy6]

It sounds like you're looking for:

=INDIRECT("'"&G12&"'!A3")+INDIRECT("'"&H12&"'!A3")

I'm getting a #REF! Error with the above formula. Not sure why

 

[Levi B]

I'm getting a #REF! Error with the above formula. Not sure why

It may be that the values in cells G12 and H12 don't exactly match the sheet names that you want to pick values from? Other than that I don't have any ideas sorry.