# Sum Indirect function through multiple sheets

1. ## 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?

André

2. ## Re: Sum Indirect function through multiple sheets

Try...

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

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

Hope this helps!

In article <OPvee.4030\$31.2745@news-server.bigpond.net.au>,
"Andre Croteau" <milandre@bigpond.net.au> 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?
>
>
> André

3. ## Re: Sum Indirect function through multiple sheets

Hello Dominic,

It works well!

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

Thanks a lot!

André

"Domenic" <domenic22@sympatico.ca> wrote in message
news:domenic22-92BF6E.21183705052005@msnews.microsoft.com...
> Try...
>
> =SUMPRODUCT(SUMIF(INDIRECT("'"&C1:C10&"'!A1:A5"),"<>"))
>
> ...where C1:C10 contains your sheet names.
>
> Hope this helps!
>
> In article <OPvee.4030\$31.2745@news-server.bigpond.net.au>,
> "Andre Croteau" <milandre@bigpond.net.au> 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?
> >
> >
> > André

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