SUMIF formula using data from another file

[Hugh]

I am attempting to use a SUMIF formula that uses a criteria range and sum
range in another file. The formula works fine as long as both files. When
the second file is closed the formula returns "#Value".

Is there a way to get it to return the last known data until the link is
updated without having to have both files open.

I am running 2007 and the formula reads =SUMIF('filepath[filename]sheet
name'!$A$13:$A$23,"CR", 'file path[file name]sheet name'!$S$13:$W$23)

Thanks in advance

 

[T. Valko]

You can use the SUMPRODUCT function. It works on closed files.

Your formula without the path:

=SUMIF($A$13:$A$23,"CR", $S$13:$W$23)

As written, the sum_range is limited to S13:S23. Do you really want it to
include S13:W23?

For just S13:S23 -

=SUMPRODUCT(--(A13:A23="CR"),S13:S23)

For S13:W23 -

=SUMPRODUCT((A13:A23="CR")*S13:W23)

Note that with the 2nd example, if there are any text entries in S12:W23 the
formula will return an error.

 

[Tom Hutchins]

Use SUMPRODUCT instead of SUMIF. SUMPRODUCT will work with a closed workbook.
A SUMPRODUCT formula patterned after your example might look like:

=SUMPRODUCT(--('filepath[filename]sheet name'!$A$13:$A$23="CR"), 'file
path[file name]sheet name'!$S$13:$W$23)

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

Hope this helps,

Hutch