Excel 2007 : SUMIFS function & #VALUE error

SUMIFS like SUMIF, COUNTIF, COUNTIFS can not be used with closed targets... you would need to revert to the less efficient SUMPRODUCT equivalent.

I'll see if I can use another function to do the same

The (big) clue was in my reply.... with master file open insert the below into your report cell:

=SUMPRODUCT(--('[master test.xlsm]Sheet1'!$G$2:$M$2=$D$2),'[master test.xlsm]Sheet1'!$G$4:$M$4)

When you close the file the above will still calculate correctly.

Note:

Though it does not help in this instance (given closed target restriction) remember that you only need use SUMIFS if you have > 1 conditions, if you have only a single test (as is the case here) then a SUMIF would suffice.

Maybe something like:

=SUMPRODUCT(--('[master test.xlsm]Sheet1'!$G$2:$M$2=$D$2),'[master test.xlsm]Sheet1'!$G$4:$M$4)/SUMPRODUCT(--('[master test.xlsm]Sheet1'!$G$2:$M$2=$D$2)

or

=AVERAGE(IF('[master test.xlsm]Sheet1'!$G$2:$M$2=$D$2,'[master test.xlsm]Sheet1'!$G$4:$M$4))

confirmed with CTRL+SHIFT+ENTER not just ENTER