Sum Indirect function through multiple sheets
 

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é

Try...

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

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

Hope this helps!

In article ,
"Andre Croteau" wrote:

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é

Hello Dominic,

It works well!

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

Thanks a lot!

André



"Domenic" wrote in message
...
Try...

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

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

Hope this helps!

In article ,
"Andre Croteau" wrote:

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é