Sumif on multiple worksheets

[Fritz]

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.

 

[T. Valko]

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

 

[Fritz]

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]

You're welcome. Thanks for the feedback!