SUMIF inside an IF Statement linked to a closed workbook

**JohnGault82** · 01-31-2011, 10:56 AM

Here's my problem. My company receives updated reports from a vendor each week. Each week, there are updated values for instances that appeared on previous reports. We want to be able to combine and compare the reports each week and each month.

So I created a summary spreadsheet that began doing just that. It seemed pretty easy until i ran into a big problem. The problem is that it appears that the SUMIF formula will not work if it is linked to a closed workbook and inside of an IF statement.

Here's my formula:

=IF($A$1 = "Certain Word",SUMIF('C:\Users\username\ldesktop\folder\subfolder\[filename.xlsx]Sheet1'!$F$3:$F$1048576,">="&DATE(2010,1,1),'C:\Users\username\ldesktop\folder\subfolder\[filename.xlsx]Sheet1'!$U$3:$U$1048576),SUM('C:\Users\username\ldesktop\folder\subfolder\[filename.xlsx]Sheet1'!$U$3:$U$1048576))

In summary, =IF(something_happens,SUMIF(sum_values_when_after_Jan.1,2010),if_not_Sum_all_values).

Overall, pretty basic stuff. However, if the linked source file is closed the SUMIF portion of the statement won't work. The normal SUM function will work. If the source file is open, the SUMIF and SUM function will work.

Does anyone know of a way to resolve this issue?

I'm using Excel 2007 and the Summary file containing the above formula is in a different folder than the source information. I tried putting the Summary file in the same folder as the Source and it doesn't work.

Please help because all the other solutions to problem quickly make files that are way too cumbersome.

**DonkeyOte** · 01-31-2011, 11:01 AM

The IF is irrelevant the issue is simply that SUMIF won't work with closed files (all other parts of your function will work ok).

Re: SUMIF you must use an Array or SUMPRODUCT equivalent but you should note these will be less efficient.

Please Login or Register  to view this content.

however you should REALLY avoid using such large ranges when using SUMPRODUCT - keep as lean as possible but note that Dynamic Ranges are not viable here given closed target (ie same restrictions as SUMIF)

**JohnGault82** · 01-31-2011, 12:01 PM

Thank you for the response. The formula as you represented it does not work. It returns a 0 value.

I had never used SUMPRODUCT before so I became curious. I searched around and found a very helpful post that demonstrated the reason why I received a 0 and how to solve it.

Please refer to the following link: http://www.exceluser.com/explore/sumproduct_12.htm

If you removed the "," in your formula and change it to "*" then it works perfectly, whether the source file is open or closed. Also, the link above explains the reason why using such a long column reference name would be less efficient.

Thank you for your post.

**DonkeyOte** · 01-31-2011, 12:12 PM

Originally Posted by JohnGault82

The formula as you represented it does not work. It returns a 0 value.
...
If you removed the "," in your formula and change it to "*" then it works perfectly

The above is not quite accurate - the formula "works" in so far as it calculates, however, to be clear - the only reason:

Please Login or Register  to view this content.

would return 0 and

Please Login or Register  to view this content.

would return otherwise is that the value_array in your data set contains values that require coercion (eg numbers stored as text)

You can confirm this by running:

Please Login or Register  to view this content.

I suspect the above will return 0 (or at least not the number you expect)

You should be aware that using explicit coercion (*) in this manner leaves you susceptible to #VALUE! errors should any cell within the U range contain a non-numeric string (eg "apple")

If you're particularly interested in SUMPRODUCT be sure to read Bob Phillips' white paper (link in sig.)

**daddylonglegs** · 01-31-2011, 12:18 PM

...also if that range is text-formatted then surely the original SUMIF wouldn't work and neither will the SUM part of the formula if A1 <> "Certain word"

**JohnGault82** · 01-31-2011, 12:40 PM

DonkeyOte - I see what you are saying, and I'll take not of that as I build this thing. Where is that white paper by Bob Phillips?

Daddylonglegs - The SUMIF formula returned the correct value on the same set of data that the SUMPRODUCT is returning a 0 value without coercion and actual value with coercion.

**DonkeyOte** · 01-31-2011, 12:41 PM

Originally Posted by JohnGault82

Where is that white paper by Bob Phillips?

see the link "SUMPRODUCT" in my sig. below

**daddylonglegs** · 01-31-2011, 12:57 PM

Originally Posted by JohnGault82

Daddylonglegs - The SUMIF formula returned the correct value on the same set of data that the SUMPRODUCT is returning a 0 value without coercion and actual value with coercion.

Perhaps I'm missing something but as far as I can see DonkeyOte has suggested that you use a formula of this type

=IF(A1="x",SUMPRODUCT((range1>date)+0,range2),SUM(range2))

as DonkeyOte says, if you get zero with that unexpectedly but it works like this

=IF(A1="x",SUMPRODUCT((range1>date)*range2),SUM(range2))

then range2 must be text formatted....

...in which case I suggest that you won't get the right result when A1<>"x" because range 2 must be text-formatted and therefore SUM(range2) will also return zero, you'd have to introduce "co-ercion" into that part of the formula too.

Are you sure that you kept the +0 in the original?