XL98: SUMIF, COUNTIF, COUNTBLANK Return #VALUE! Error If They Reference a Closed File

ID: Q179029

The information in this article applies to:

Microsoft Excel 98 Macintosh Edition

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

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

array formulas, enter

and then double-click the selected text to go to the "array formulas, entering and editing" topic.

Additional query words: XL98

Keywords : xlformula
Version : MACINTOSH:98
Platform : MACINTOSH
Issue type : kbbug