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.

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

--
Biff
Microsoft Excel MVP


"Fritz" wrote in message
...
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.

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!


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Fritz" wrote in message
...
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.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Fritz" wrote in message
...
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!


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Fritz" wrote in message
...
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.