Microsoft KB Archive/179029

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

Article ID: 179029

Article Last Modified on 9/11/2002

-

APPLIES TO


 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q179029



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

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



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 the Macintosh, press COMMAND+RETURN.

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 this to be a problem in the products listed at the beginning of this article.



MORE INFORMATION
To start the wizard that helps you create these functions in Microsoft Excel 98 Macintosh Edition, point to Wizard on the Tools menu and click Conditional Sum.

The SUMIF function uses the following syntax:

=SUMIF(range, criteria,sum_range).

