SUMPRODUCT across worksheets

I am familiar with using SUMPRODUCT(--(criterion=range1),(range2)) to obtain
results not possible with SUMIF. However, I would now like to apply this to,
say, all D5 cells in many contiguous worksheets. I get error results. Can
someone confirm that this is not possible to do? Any suggestions for a way
around this?
Many thanks,
Stephen Powell

If you only have one criteria like in a sumif and either sum the same range
or another range you can use this:
First you need to create a list with ALL (not like in 3-D excel first and
last) sheet names or if they have the same aplha name plus numbers you can do
it as well.
1. if they all have different names and you put all sheet names in range
H1:H20
and let's say you want to sum B1:B100 in all sheets where A1:A100 is criteria1
then you can use this

=SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$20&"'!A1:A100"),"criteria1",INDIRECT("'"&$H$1:$H$20&"'!B1:B100")))


if the names are identical text plus different numbers like Sheet1, Sheet2
amd so one then you can use

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:20"))&"'!A1:A100"),"criteria1",INDIRECT("'Sheet"&ROW(INDIRECT("1:20"))&"'!B1:B100")))

if you need more criteria and more ranges may I recommend Morefun by Laurent
Longre

It's an excellent add-in that has some nice 3-D features

http://longre.free.fr/english/

here's a description in English


http://www.rhdatasolutions.com/morefunc/


Regards,

Peo Sjoblom

"Stephen POWELL" wrote:

> I am familiar with using SUMPRODUCT(--(criterion=range1),(range2)) to obtain
> results not possible with SUMIF. However, I would now like to apply this to,
> say, all D5 cells in many contiguous worksheets. I get error results. Can
> someone confirm that this is not possible to do? Any suggestions for a way
> around this?
> Many thanks,
> Stephen Powell

Peo:
Thank you kindly. Your solution works nicely. I've never used INDIRECT
before and it is not intuitively obvious to me why it works - I will have to
research this function to understand it.
Thanks again.
Stephen

"Peo Sjoblom" wrote:

> If you only have one criteria like in a sumif and either sum the same range
> or another range you can use this:
> First you need to create a list with ALL (not like in 3-D excel first and
> last) sheet names or if they have the same aplha name plus numbers you can do
> it as well.
> 1. if they all have different names and you put all sheet names in range
> H1:H20
> and let's say you want to sum B1:B100 in all sheets where A1:A100 is criteria1
> then you can use this
>
> =SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$20&"'!A1:A100"),"criteria1",INDIRECT("'"&$H$1:$H$20&"'!B1:B100")))
>
>
> if the names are identical text plus different numbers like Sheet1, Sheet2
> amd so one then you can use
>
> =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:20"))&"'!A1:A100"),"criteria1",INDIRECT("'Sheet"&ROW(INDIRECT("1:20"))&"'!B1:B100")))
>
> if you need more criteria and more ranges may I recommend Morefun by Laurent
> Longre
>
> It's an excellent add-in that has some nice 3-D features
>
> http://longre.free.fr/english/
>
> here's a description in English
>
>
> http://www.rhdatasolutions.com/morefunc/
>
>
> Regards,
>
> Peo Sjoblom
>
> "Stephen POWELL" wrote:
>
> > I am familiar with using SUMPRODUCT(--(criterion=range1),(range2)) to obtain
> > results not possible with SUMIF. However, I would now like to apply this to,
> > say, all D5 cells in many contiguous worksheets. I get error results. Can
> > someone confirm that this is not possible to do? Any suggestions for a way
> > around this?
> > Many thanks,
> > Stephen Powell

My pleasure, thanks for the feedback

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Stephen POWELL" <[email protected]> wrote in message
news:[email protected]...
> Peo:
> Thank you kindly. Your solution works nicely. I've never used INDIRECT
> before and it is not intuitively obvious to me why it works - I will have
> to
> research this function to understand it.
> Thanks again.
> Stephen
>
> "Peo Sjoblom" wrote:
>
>> If you only have one criteria like in a sumif and either sum the same
>> range
>> or another range you can use this:
>> First you need to create a list with ALL (not like in 3-D excel first and
>> last) sheet names or if they have the same aplha name plus numbers you
>> can do
>> it as well.
>> 1. if they all have different names and you put all sheet names in range
>> H1:H20
>> and let's say you want to sum B1:B100 in all sheets where A1:A100 is
>> criteria1
>> then you can use this
>>
>> =SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$20&"'!A1:A100"),"criteria1",INDIRECT("'"&$H$1:$H$20&"'!B1:B100")))
>>
>>
>> if the names are identical text plus different numbers like Sheet1,
>> Sheet2
>> amd so one then you can use
>>
>> =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:20"))&"'!A1:A100"),"criteria1",INDIRECT("'Sheet"&ROW(INDIRECT("1:20"))&"'!B1:B100")))
>>
>> if you need more criteria and more ranges may I recommend Morefun by
>> Laurent
>> Longre
>>
>> It's an excellent add-in that has some nice 3-D features
>>
>> http://longre.free.fr/english/
>>
>> here's a description in English
>>
>>
>> http://www.rhdatasolutions.com/morefunc/
>>
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>> "Stephen POWELL" wrote:
>>
>> > I am familiar with using SUMPRODUCT(--(criterion=range1),(range2)) to
>> > obtain
>> > results not possible with SUMIF. However, I would now like to apply
>> > this to,
>> > say, all D5 cells in many contiguous worksheets. I get error results.
>> > Can
>> > someone confirm that this is not possible to do? Any suggestions for a
>> > way
>> > around this?
>> > Many thanks,
>> > Stephen Powell