SumifS Multiple Sum Ranges
 

Is it possible to sum ranges from more than one worksheet. I know I do a
Sumifs + Sumifs, but would rather not.

Thank you.

--
Thanks!

Dee

SumifS Multiple Sum Ranges
 

Create a list with the names of all sheets in this workbook that you want to
include in your sumif, assume you have 10 sheets and you put the names in
H1:H10, then you can use for example

=SUMPRODUCT(SUMIF(INDIRECT("'"&H1:H10&"'!A1:A100") ,"Yes",INDIRECT("'"&H1:H10&"'!B1:B100")))


that would be the equivalent of

=SUMIF(A1:A100,"Yes",B1:B100)

where you would sum all entries in B1:B100 where A1:A100 is Yes


--
Regards,

Peo Sjoblom




"dee" wrote in message
...
Is it possible to sum ranges from more than one worksheet. I know I do a
Sumifs + Sumifs, but would rather not.

Thank you.

--
Thanks!

Dee

SumifS Multiple Sum Ranges
 

Thank you for your response. I really just wanted clarification that I
cannot perform Sumifs, Averageifs, etc. by selecting multiple sum, average,
count, ranges.

What I finally did was, for example:

=AVERAGE(AVERAGEIFS(Jan!$D$4:$D$68,Jan!$E$4:$E$68, Summary!$A25,Jan!$A$4:$A$68,"isd*"),AVERAGEIFS(Feb !$D$4:$D$65,Feb!$E$4:$E$65,Summary!$A25,Feb!$A$4:$ A$65,"isd*"))

That seemed to do the trick.
--
Thanks!

Dee


"Peo Sjoblom" wrote:

Create a list with the names of all sheets in this workbook that you want to
include in your sumif, assume you have 10 sheets and you put the names in
H1:H10, then you can use for example

=SUMPRODUCT(SUMIF(INDIRECT("'"&H1:H10&"'!A1:A100") ,"Yes",INDIRECT("'"&H1:H10&"'!B1:B100")))


that would be the equivalent of

=SUMIF(A1:A100,"Yes",B1:B100)

where you would sum all entries in B1:B100 where A1:A100 is Yes


--
Regards,

Peo Sjoblom




"dee" wrote in message
...
Is it possible to sum ranges from more than one worksheet. I know I do a
Sumifs + Sumifs, but would rather not.

Thank you.

--
Thanks!

Dee