If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
SUMIF formula using data from another file
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
SUMIF formula using data from another file
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 he http://www.xldynamic.com/source/xld.SUMPRODUCT.html Hope this helps, Hutch "Hugh" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|