SUMIF, COUNTIF, COUNTBLANK Return #VALUE! Error (260415)
The information in this article applies to:
- Microsoft Excel 2000
- Microsoft Excel 2002
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.
ExamplesNOTE: 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
This behavior is by design.REFERENCES
For more information about a wizard that can help you create these functions, click Microsoft Excel Help on the
Help menu, type summarize values that meet conditions by using the conditional sum wizard in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
For more information about array formulas, click Microsoft Excel Help on the
Help menu, type about using formulas to calculate values on other worksheets and workbooks in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
| Modification Type: | Major | Last Reviewed: | 5/30/2006 |
|---|
| Keywords: | kbbug kbpending KB260415 |
|---|
|