SUMIF used in a link

Hello,
I have a destination file where I'm using a SUMIF function
referencing the source data saved in another workbook.
When both files (the destination file with the SUMIF and
the source file are opened) I see the results of the SUMIF
calculations displayed. When I open only a destination
file, I'm prompted to "Update Links" or "Do not Update"
the links. If either option ('Update" or "Do not Update")
is chosen the file opens but the results of the
calculations are displayed as #VALUE error.

Any idea why it is happening? It doesn't happen if I use
a different function like VLOOKUP or SUM (I mean the
results of the calculations are displayed when "Do not
Update" option is chosen). Is it a limitation of the
SUMIF function (because it works if the source data is in
the same workbook but different worksheet)? Please help.
Thank you.

"Nat" wrote...
I have a destination file where I'm using a SUMIF function
referencing the source data saved in another workbook.
When both files (the destination file with the SUMIF and
the source file are opened) I see the results of the SUMIF
calculations displayed. When I open only a destination
file, I'm prompted to "Update Links" or "Do not Update"
the links. If either option ('Update" or "Do not Update")
is chosen the file opens but the results of the
calculations are displayed as #VALUE error.

Any idea why it is happening? It doesn't happen if I use
a different function like VLOOKUP or SUM (I mean the
results of the calculations are displayed when "Do not
Update" option is chosen). Is it a limitation of the
SUMIF function (because it works if the source data is in
the same workbook but different worksheet)? Please help.

SUMIF and COUNTIF *REQUIRE* that their 1st argument (and optional 3rd argument
for SUMIF) be range references. External link references are range references
only when the linked workbook is open; they're arrays when the linked workbook
is closed. Since SUMIF and COUNTIF require range references, they choke on array
references to ranges in closed files.

Use SUMPRODUCT instead. For example, replace

SUMIF(X,"=Y"), COUNTIF(X,"Y") and SUMIF(X,"*Y*",Z)

with

SUMPRODUCT((X=Y)*X), SUMPRODUCT(--(XY)) and
SUMPRODUCT(ISNUMBER(SEARCH(Y,X)*Z), respectively.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.