Sumif/Averageif error linking outside workbook

I am having problems updating my sumif or averageif formula if it is referencing data outside the current workbook. The data only populates when the source workbook opens. When the source workbook is closed, the cell returns a #VALUE! error. Does anyone know how I can fix this error so I don't have to open the source file? (FYI link directory have been confirmed correct).

those formulas are volatile, you can get around it by using a non-volatile formula such as SUMPRODUCT to do the same task.