SUMIF used in a link

  • Thread starter Thread starter Nat
  • Start date Start date
N

Nat

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.
 
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.
Click to expand...

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(--(X<>Y)) and
SUMPRODUCT(ISNUMBER(SEARCH(Y,X)*Z), respectively.
 
Back
Top