Array Formula Value Error

I have the below array formula which checks 2 criteria then pulls out the sum of the matched results. When I open the sheet it displays as a #Value error. If I go into the formula and press ctrl + shift + enter it calculates the value (array formula). How can I adjust the formula to display the result when I open the sheet without having to go into it each time?


=SUMIF(sheet1!$C$3:$BP$3,C5,INDIRECT(CONCATENATE("sheet1!$C$",MATCH(Sheet2!D5 & Sheet2!E5,sheet1!$E$1:$E$10000 & sheet1!$C$1:$C$10000,0),":$BP$",MATCH(Sheet2!D5 & Sheet2!E5,sheet1!$E$1:$E$10000 & sheet1!$C$1:$C$10000,0))))

Not any issue with array formula. This is the problem with sheets, make sure all the sheets are open.

There is only 1 workbook. The sheets/tabs are open in the workbook.

I recommend using a helper column here (they're not a bad thing)
ON Sheet1, say column F
F1 and filled down
=E1&C1
Then use this non array entry formula.
=SUMIF(sheet1!$C$3:$BP$3,C5,INDEX(Sheet1!$C$1:$BP$10000,MATCH(Sheet2!D5 & Sheet2!E5,sheet1!$F$1:$F$10000,0),0))