External Reference issue with SUMIF

I have a main spreadsheet that I want to use SUMIF formulas to read data in other spreadsheets on the same network/same drive. Every time I try to reopen the main file the values are #VALUE until I r...