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.
REFERENCES
For more information about array formulas in Microsoft Excel 98 Macintosh
Edition, click Contents and Index on the Help menu, click the Index button,
type the following text
and then double-click the selected text to go to the "array formulas,
entering and editing" topic.
Additional query words: XL98
Keywords: kbbug KB179029