Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF - my formula misreads after data has been sorted | Excel Worksheet Functions | |||
How can I pull data from a different file from within a formula? | Excel Discussion (Misc queries) | |||
Creating a formula by using data in another file | Excel Worksheet Functions | |||
Q: Can a formula reference a cell to get the file name to link to for data? | Excel Discussion (Misc queries) | |||
Formula to get Relative Folder Reference to data in another file? | Excel Worksheet Functions |