Sumif on multiple worksheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fritz - 16 May 2008 03:03 GMT

Hi I have a formula below that I want to expand to more than one worksheet.
I've played with indirect from other examples but I just don't get it right.

In a summary worksheet I want to bring this total in for all worksheets.
=SUMIF('23050'!B2:B166,"*3220*",'23050'!C2:C166)

the other worksheets are called
23060,23070,23080,23100,23110,23120,23130,23140

I want to expand this to other references than "*3220*" which I'm fine with
but not sure how to use the indirect correctly to cover all the worksheets.

Reply to this Message

T. Valko - 16 May 2008 03:29 GMT

List your sheet names in a range of cells:

J1 = 23050
J2 = 23060
J3 = 23070
..
J9 = 23120

Then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&J1:J9&"'!B2:B166"),"*3220*",INDIRECT("'"J1:J9&"'!C2:C166")))

Signature

Biff
Microsoft Excel MVP

> Hi I have a formula below that I want to expand to more than one
> worksheet.
[quoted text clipped - 11 lines]
> but not sure how to use the indirect correctly to cover all the
> worksheets.

Reply to this Message

Fritz - 16 May 2008 03:53 GMT

Thanks This worked a treat and I wasn't sure if I count reference a list on
another worksheet - tried it and I can so even better!

> List your sheet names in a range of cells:
>
[quoted text clipped - 23 lines]
> > but not sure how to use the indirect correctly to cover all the
> > worksheets.

Reply to this Message

T. Valko - 16 May 2008 04:23 GMT

You're welcome. Thanks for the feedback!

Signature

Biff
Microsoft Excel MVP

> Thanks This worked a treat and I wasn't sure if I count reference a list
> on
[quoted text clipped - 28 lines]
>> > but not sure how to use the indirect correctly to cover all the
>> > worksheets.

Reply to this Message