D
dee
Is it possible to sum ranges from more than one worksheet. I know I do a
Sumifs + Sumifs, but would rather not.
Thank you.
Sumifs + Sumifs, but would rather not.
Thank you.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
Peo Sjoblom
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
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
D
dee
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
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 said: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