Sumif formula returning #VALUE when source worksheet is not open

[Gillian]

Hi,
In Workbook1 have a sumif formula which is summing data from Workbook 2.
When Workbook 2 is open the formula works fine. When Workbook 2 is closed
the formula returns #VALUE error.
Can you help?
thanks

 

[yclhk]

In Workbook 1, edit -> link; in the Edit Link dialog box to confirm the
source link is linked to Workbook 2, save Workbook1. Once, Workbook1 is
opened, a box is prompted to update the link or not, click Yes. It is no
need to open Workbook2.

Y C Leung

 

[Pete_UK]

SUMIF doesn't work with closed workbooks - try using SUMPRODUCT
instead.

If you need help to convert the formula then post what you have here.

Hope this helps.

Pete

 

[Gillian]

Hi
thanks
the formula I have is
=SUMIF([Book1.xlsx]Sheet1!$A:$A,A1,[Book1.xlsx]Sheet1!$B:$B)

I've not used sumproduct before
thanks for your help

 

[Pete_UK]

Well, you are obviously using XL2007, so you can use full-column
references with SUMPRODUCT. The formula you quote implies that
Book1.xlsx is open, because otherwise you would have the full path in
front of the filename. It is better to start in this situation, and
then when you close Book1.xlsx Excel will insert the full path for
you. So here is a replacement formula:

=SUMPRODUCT(--([Book1.xlsx]Sheet1!$A:$A=A1),[Book1.xlsx]Sheet1!$B:$B)

Then when you close Book1.xlsx this formula will expand to include the
full path.

However, you might find that this takes a long time to calculate,
given the number of cells in a full column. If you only have 300 cells
of data, say, then it would be more efficient to use specific ranges,
like this:

=SUMPRODUCT(--([Book1.xlsx]Sheet1!$A$1:$A$300=A1),[Book1.xlsx]Sheet1!$B
$1:$B$300)

Hope this helps.

Pete

Hi
thanks
the formula I have is
=SUMIF([Book1.xlsx]Sheet1!$A:$A,A1,[Book1.xlsx]Sheet1!$B:$B)

I've not used sumproduct before
thanks for your help

--
GJ

SUMIF doesn't work with closed workbooks - try using SUMPRODUCT
instead.

If you need help to convert the formula then post what you have here.

Hope this helps.

- Show quoted text -