#REF from external spreadsheets

[Guest]

I have a summary spreadsheet that has "sumif's" that call out to separate spreadsheets. Each of those separate spreadsheets is about 1mb in size and located across the WAN. If I open up all of the separate spreadsheets first and then open the summary spreadsheet, everything works fine. If I only open the summary spreadsheet I get #REF's in some of the fields. I can only assume this is because it is timing out going to get data from the other spreadsheets. Is there anything I can do about this? A timeout setting somewhere that I can increase

Thanks in advance.

 

[Frank Kabel]

Hi
SMIF won't work with closed workbooks. You may convert your SUMIF
formulas to SUMPRODUCT (which will work with closed workbooks). e.g.
convert them like the following:
=SUMIF(A1:A100,"value",B1:B100)
to
=SUMPRODUCT(--(A1:A100="value"),B1:B100)
Note: SUMPRODUCT won't accept ranges like A:A

 

[Frank Kabel]

Hi
1. The double minus coerces the boolean values to real numbers
(TRUE=1/FALSE=0)
2. Try
=SUMPRODUCT(--(A1:A100={"value1","value2}),B1:B100)

or
=SUMPRODUCT(--((A1:A100="value1")+(A1:A100="value2)>0),B1:B100)