Converting SUMIFS to array formula

[gohawks222]

I have two formulas linked to other workbooks, but I'm getting the #VALUE error when they are closed (they work fine when everything is open). I've tried to figure out how to do this, but I don't have a ton of experience with array formulas and I'm having trouble with it. I'm hoping someone can convert these and explain the reasoning behind it. Thanks!

1.

=E2/(SUMIFS(INDEX([MasterItemFile_Customer.xlsx]Summary!$A$1:$FU$16887, 0, MATCH("Avg- 6WK", [MasterItemFile_Customer.xlsx]Summary!$A$1:$FU$1,0)), [MasterItemFile_Customer.xlsx]Summary!$E$1:$E$16887, B2)+SUMIFS(INDEX([MasterItemFile_Customer.xlsx]Summary!$A$1:$FU$16887, 0, MATCH("Avg- 6WK", [MasterItemFile_Customer.xlsx]Summary!$A$1:$FU$1,0)), [MasterItemFile_Customer.xlsx]Summary!$E$1:$E$16887, C2))

2.

=(SUMIFS(INDEX('[FY16 ForecastMaster.xlsm]Orders'!$B$2:$NW$357, 0, MATCH(DateMatrix!$F$1, '[FY16 ForecastMaster.xlsm]Orders'!$B$2:$NW$2,0)), '[FY16 ForecastMaster.xlsm]Orders'!$B$2:$B$357, Sheet1!D2)+SUMIFS(INDEX('[FY16 ForecastMaster.xlsm]Orders'!$B$2:$NW$357, 0, MATCH(DateMatrix!$F$2, '[FY16 ForecastMaster.xlsm]Orders'!$B$2:$NW$2,0)), '[FY16 ForecastMaster.xlsm]Orders'!$B$2:$B$357, Sheet1!D2)+SUMIFS(INDEX('[FY16 ForecastMaster.xlsm]Orders'!$B$2:$NW$357, 0, MATCH(DateMatrix!$F$3, '[FY16 ForecastMaster.xlsm]Orders'!$B$2:$NW$2,0)), '[FY16 ForecastMaster.xlsm]Orders'!$B$2:$B$357, Sheet1!D2))/3

 

[gohawks222]

bump