SUMIF formula only works when linked spreadhseet is open

**jezza1** · 09-23-2009, 08:34 PM

hi all,

I have a SUMIF function in my 'main' workbook which is looking at data in a different 'source' workbook. When that source workbook is open, the formula works correctly, but when the source workbook isn't open, my SUMIF returns #VALUE!

Any idea how I resolve this (opening the source workbook everytime as well as the main workbook is not an option)?

Many thanks in advance...

**teylyn** · 09-23-2009, 09:46 PM

read http://support.microsoft.com/kb/260415

A formula that contains the SUMIF, COUNTIF, or COUNTBLANK functions may return the #VALUE! error in Microsoft Excel.

Note This behavior also applies to the Dfunctions, such as DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, and DVARP.

This behavior occurs when the formula that contains the SUMIF, COUNTIF, or COUNTBLANK function refers to cells in a closed workbook.

Note If you open the referenced workbook, the formula works correctly.

To work around this behavior, use a combination of the SUM and IF functions together in an array formula.
Back to the top
Examples
Note You must enter each formula as an array formula. To enter a formula as an array formula in Microsoft Excel for Windows, press CTRL+SHIFT+ENTER.
SUMIF
Instead of using a formula that is similar to the following
=SUMIF([Source]Sheet1!$A$1:$A$8,"a",[Source]Sheet1!$B$1:$B$8)
use the following formula:
=SUM(IF([Source]Sheet1!$A$1:$A$8="a",[Source]Sheet1!$B$1:$B$8,0))

etc.

**jezza1** · 09-23-2009, 10:30 PM

Hi Teylyn,

Thanks for the quick response. I was hoping for a quicker solution but alas, not to be! The work-around should be OK though.

cheers

Jezza

**DonkeyOte** · 09-24-2009, 04:14 AM

Note also that you can also use SUMPRODUCT in these instances.

There is some debate as to whether or not SUMPRODUCT is any quicker than a CSE Array (they are processed in similar fashion), however, there is one key difference between the two and that is (from an end users perspective) SUMPRODUCT is slightly more robust insofar as there is no need for CTRL + SHIFT + ENTER when committing the formula.

Using the quote from teylyn's post:

Code:

=SUM(IF([Source]Sheet1!$A$1:$A$8="a",[Source]Sheet1!$B$1:$B$8,0))
confirmed with CTRL + SHIFT + ENTER

can be written in SUMPRODUCT terms as

Code:

=SUMPRODUCT(--([Source]Sheet1!$A$1:$A$8="a"),[Source]Sheet1!$B$1:$B$8)
confirmed with ENTER as normal

**Bond007** · 10-26-2009, 11:02 PM

Hey guys,
I have a smiliar problem. We're using SUMIF at work and it won't work unless the external excel file is open as well.

This is the formula we're using:

Code:

=SUMIF('[Xl0000006.xls]RCF 09'!Provider_ID,B8,Xl0000006.xls!Beds)

I've been reading that I would change it to SUMPRODUCT or another similar formula that doesn't have this issue but I have not had any luck.

Do you guys have any suggestions?

Thanks