SUMIF across multiple worksheets.

[Daniel Dejeu]

Is there a way to use SUMIF across multiple worksheets?

Thank you,

Daniel Dejeu

 

[RagDyer]

Not by itself, but in various combinations.

Easiest is probably to make a datalist of your WS names,
then assign a name to the list,
and use that in conjunction with Sumproduct().

If you have 5 sheets, say list in Z1 to Z5.
Make sure spelling of sheet names are exactly the same as they appear in the
tabs.

Select Z1 to Z5,
click in the name box (left of formula bar),
type in something short, such as
list
then hit <Enter>

Then, try something like this:
To total B1 to B10 on all the sheets, where values are greater then 200:

=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!B1:B10"),">200"))

To total C1 to C10 on all the sheets, where the corresponding values in B1
to B10 are greater then 200:

=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!B1:B10"),">200",INDIRECT("'"&list&"'!C1:C10")))

 

[Daniel Dejeu]

This is working even better than what I had in mind.
Thanks a lot RD.

Daniel Dejeu

 

[RagDyeR]

You're welcome, and thank you for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

This is working even better than what I had in mind.
Thanks a lot RD.

Daniel Dejeu