SUMIF formula using data from another file
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.
--
Biff
Microsoft Excel MVP
"Hugh" wrote in message
...
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
|