A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

SUMIF formula using data from another file



 
 
Thread Tools Display Modes
  #1  
Old April 14th, 2009, 04:05 AM posted to microsoft.public.excel.worksheet.functions
Hugh
external usenet poster
 
Posts: 84
Default 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  
Old April 14th, 2009, 04:54 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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  
Old April 14th, 2009, 05:02 AM posted to microsoft.public.excel.worksheet.functions
Tom Hutchins
external usenet poster
 
Posts: 722
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:05 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.