There are some functions that won't work unless the sending file is open.
=sumif(), =countif(), =indirect()
are a few.

You could replace the formula with the equivalent =sumproduct()

=SUMPRODUCT(--([book1.xls]Sheet1!$A$5:$A$1000="Something"),
[book1.xls]Sheet1!F$5:F$1000)
......

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Erin wrote:

Hi All,

looking for a resolution - I don't know why this is happening, but I'm using
SUMIF formulae that references external links (source data is in external
file). The correct value is retrieved when the source file is open, however,
when the workbook is closed, I get a #VALUE error. Since I have heaps of
formulae in this workbook (and a couple of others) relying on this external
source file, I want to make sure that I can view the correct values when the
source file is closed. Also - if I send the report to team members, I want
them to be able to view the data without having to open the source file.
Any ideas on how to get this link to work when the source file(s) is closed?

Thanks!!

--

Dave Peterson