Is there a way to use SUMIF across multiple worksheets?

Thank you,

Daniel Dejeu

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")))


--
HTH,

RD

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


"Daniel Dejeu" <Daniel wrote in message
...
Is there a way to use SUMIF across multiple worksheets?

Thank you,

Daniel Dejeu

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

Daniel Dejeu

"RagDyer" wrote:

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")))


--
HTH,

RD

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


"Daniel Dejeu" <Daniel wrote in message
...
Is there a way to use SUMIF across multiple worksheets?

Thank you,

Daniel Dejeu

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

Regards,

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

"Daniel Dejeu" wrote in message
...
This is working even better than what I had in mind.
Thanks a lot RD.

Daniel Dejeu

"RagDyer" wrote:

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")))


--
HTH,

RD

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


"Daniel Dejeu" <Daniel wrote in message
...
Is there a way to use SUMIF across multiple worksheets?

Thank you,

Daniel Dejeu