Microsoft KB Archive/260415

= SUMIF, COUNTIF, COUNTBLANK Return #VALUE! Error =

Article ID: 260415

Article Last Modified on 2/2/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q260415





For a Microsoft Excel 98 version of this article, see 179029.



For a Microsoft Excel 97 version of this article, see 237188.



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



CAUSE
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.



WORKAROUND
To work around this behavior, use a combination of the SUM and IF functions together in an array formula.

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,&quot;a&quot;,[Source]Sheet1!$B$1:$B$8)

use the following formula:

=SUM(IF([Source]Sheet1!$A$1:$A$8=&quot;a&quot;,[Source]Sheet1!$B$1:$B$8,0))

COUNTIF
Instead of using a formula that is similar to the following

=COUNTIF([Source]Sheet1!$A$1:$A$8,&quot;a&quot;)

use the following formula:

=SUM(IF([Source]Sheet1!$A$1:$A$8=&quot;a&quot;,1,0))

COUNTBLANK
Instead of using a formula that is similar to the following

=COUNTBLANK([Source]Sheet1!$A$1:$A$8)

use the following formula:

=SUM(IF([Source]Sheet1!$A$1:$A$8=&quot;&quot;,1,0))



STATUS
This behavior is by design.



MORE INFORMATION
The SUMIF function uses the following syntax:

=SUMIF(range, criteria, sum_range).

