Sum Indirect function through multiple sheets

[Andre Croteau]

Hello!

I have been looking through Googgle for a solution but have yet to find it.

I am trying to replicate the following 3 dimentional sum formula using the
indirect function

=SUM(START:END!A1:A5)


Suppose I have the following (without the double quotes):

in cell C1 I have the label "START"
in cell D1, I have the lable "END"
in cell E1 I have "A1"
in cell F1 I have "A5"

These are some of examples that I tried in cell B1

=SUM(INDIRECT("'"&C1&":"&D1&"'!"&E1&":"&F1))
=SUM(INDIRECT(C1&":"&D1&"!"&E1&":"&F1))
=SUM(INDIRECT(C1&":"&D1&"!A1:A5"))

For each of these trial formulas, I ended up with a #REF! result
I have yet to see an example with he indirect function used over multiple
sheets.

What am I going wrong?

Thanks in advance!

André

 

[Domenic]

Try...

=SUMPRODUCT(SUMIF(INDIRECT("'"&C1:C10&"'!A1:A5"),"<>"))

....where C1:C10 contains your sheet names.

Hope this helps!

 

[Andre Croteau]

Hello Dominic,

It works well!

It's just a bit surprising that one must revert to a sumproduct formula!

Thanks a lot!

André