Source file not automatically opening getting #value

[EVERYDAY]

"I have some files where I have sheets linked to another file but when I open the file the source file does not open automatically and i only get #VALUE. These are monthly financially statements for multiple departments that I always have to open the source to the file. But this does not happen in all the files and I am not sure why or how to fix. I have checked the setting and not sure what I am missing or should change in the settings. Although in "Edit Link" it is set on "Automatic". Is there any way to always have the values show without doing this. I have other sheets with linked files with some of the same sheets in them and they do not have this issue. I am using Excel 2016 and have sumifs in my data but as stated this only happens in a few of the file the same data just different department. All/any help and suggestions is greatly appreciated.

 

[Alex Blakenburg]

I am surprised that the SumIfs are working in any of the workbooks when they are accessing a closed workbook.
There is a fairly similar question asked and answered here.
#VALUE! Error on linked workbooks

Sumifs does not support answering closed workbook. I list of functions know to either support or not support accessing Closed Workbooks can be found here:-
Functions that Can Access Closed Workbooks