How to fix a #VALUE with a SUMIF formula on an external excel sheet

Array function such as SUMIF require that the source workbook be open as the function returns an array not a value. A VLOOKUP function does work on a closed external workbook but a SUMIF will not.

If you substitute the sumif for a vlookup then you can link a hidden sheet with all the linked cells and then do a local sumif onto the linked worksheet.