"Nickneem" wrote...
If created a formula a while ago in which I refer to sheets with names
like 1 and 2 -- ROW($1:$2) this way I get a correct amount from the
sumproduct formula.

=SUMPRODUCT(SUMIF(INDIRECT("'" & ROW($1:$2) & "'!A2:Z1000"),
$A4,INDIRECT("'" & ROW($1:$2) & "'!B2:B1000")))

I want to rename the sheets to something which makes more sense but if
I try that (like for instance to sheet1) my formula returns an error?

Your original formula worked because ROW($1:$2) returned the actual
worksheet names as an array, {1;2}. If you want to change your worksheet
names, you'd need to change the formula so that instead of ROW($1:$2) it
uses something else that returns an array of the worksheet names. Simplest
would be to enter the new worksheet names in a range, e.g., X99:x100, then
refer to that range instead.

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