strange sumifs problem, works with file open, #value with file closed

I have the below formula which is basically looking at a date in J1 in my current file, and in another file dates in B16 to B2000 and some numbers in AQ16 to AQ2000 which I want to add up (providing the corresponding date in B16-B2000 is between J1 and 6 days prior to J1. Basically I am taking the current date in J1 and adding up the past 7 days worth of data from the corresponding dates in column AQ. It works fine if I have the source file open, but whenever the file is closed and I refresh, it brings up #value, not sure why, unless sumifs will not work on external files similar to offset?

Please Login or Register  to view this content.

Sumif(s) won’t work with the closed workbooks.

Convert it to Sumproduct()