"Nickneem" wrote...
Thanks Harlan, so if I understand it correct I can't have my sheet
named like "Inv Japan", "Inv UK", "Inv USA", etc?
I really have to dig deeper into this sumproduct / indirect thing, I've
set it up once but now it looks all abracadabra to me after a couple of
months.

No. You can name your worksheets anything you want, but unless you name them
as whole numbers between 1 and 65536 you can't use ROW to generate an array
of worksheet names.

If you enter the following into X99:X102,

Inv Japan
Inv UK
Inv USA
Inv Mars Colony

and these are names of worksheets with identical layouts, then you can use
the formula

=SUMPRODUCT(SUMIF(INDIRECT("'"&$X$99:$X$102&"'!A2: Z1000"),
$A4,INDIRECT("'"&$X$99:$X$102&"'!B2:B1000")))

to sum the entries in all of these worksheets' B2:B1000 ranges where the
corresponding cell in column A of the respective worksheets matches the
value of cell A4 in the worksheet containing this formula.

So you just need to replace the ROW(..) call with something that evaluates
to an array of the names of the worksheets over which you want to sum
conditionally.