Sumproduct using multiple criteria

Hi experts,

I am trying to sum over multiple sheets dependent on single or multiple criteria.
I reproduce below the formula in one of the cells which is summing certain cells on separate sheets dependent on whether the sheet is on a named sheet.
So in the below example and attachment, any sheet defined as “UK” and matching the country in cell B10, the value contained in cell D8 of that country's sheet will be returned.
Therefore, the value 220 is returned from the Ireland sheet as Ireland is on the named list.

=SUMPRODUCT(SUMIF(INDIRECT("'"&UK&"'!B2"),$B12,INDIRECT(UK&"!"&CELL("address",K$8))))


This is all working fine. However I am struggling to write the formula for multiple criteria, so further down the sheet in the yellow filled cell I want all the Staff costs in January that are on “UK” named list (again UK & Ireland). I have made several attempts but either returning blank or ref errors. Please help.

Sbz

Thanks John. Apologies been out of the office the last week so not had chance to respond. I managed to take your solution and tweaked it slightly to give me the desired result.

No problem: glad to have a solution.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.