Excel sumifs #value when closing excel file

[GiulioB]

Hi everybody. I have used the formula sumifs in order to calculate the sum of values in a column ranging between, e.g., 100 and 1000, located in a different workbook. The problem is that when I close that excel workbook the formula in my first excel file doesn't work anymore and #value appears. I would like to find an alternative formula/solution to this, and I have already looked online, but I don't seem to be able to find a proper solution. For instance, I could use sumproduct, but as a result I only get a count of the values satisfying the conditions and not the sum of them. Could you help me? Thanks. Giulio

 

[Crystalyzer]

Seems to be a known issue

How to correct a #VALUE! error in the SUMIF/SUMIFS function

 

[GiulioB]

Thanks for your reply.
I have already read that article but the only advice it gives is to open the closed workbook and refresh the formula. I can't open every single file every time because in the excel file I am working in there are many linked source files and this would take a long time without it being a permanent solution.
Thanks
Giulio