Sum IF function - linked to external worksheets

[Dave DJ]

Excel 2000

I have a spreadsheet that utilizes the sumif function
command from another external spreadsheet. If the other
file is open, no problem, the function does its job.
However, when the external file is not open and the dialog
box asks me about updating links I get a value error in
each cell. How do I make this work without opening the
external file?

Thanks in advance.

Dave

 

[Frank Kabel]

Hi Dave
you may try using SUMPRODUCT instead as SUMIF does not work with closed
workbooks. e.g. change a formula like
=SUMIF('[Book2.xls]Tabelle1'!$A$1:$A$6,"condition",'[Book2.xls]Tabelle'
1!$B$1:$B$6)

to
=SUMPRODUCT(--('[Book2.xls]Tabelle1'!$A$1:$A$6="condition"),'[Book2.xls
]Tabelle'1!$B$1:$B$6)

this will work even with closed workbooks