Microsoft KB Archive/237188

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

Article ID: 237188

Article Last Modified on 8/27/2002

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q237188



For a Microsoft Excel 2000 and later version of this article, see 260415.

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



SYMPTOMS
A formula that contains the SUMIF, COUNTIF, or COUNTBLANK function may return the #VALUE! error.



CAUSE
This problem 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 problem, 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,"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))

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

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

use the following formula:

=SUM(IF([Source]Sheet1!$A$1:$A$8="a",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="",1,0))



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
The SUMIF function uses the following syntax:

=SUMIF(range, criteria, sum_range).

