Article ID: 237188 - Last Review: August 27, 2002 - Revision: 1.0

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

Retired KB ArticleRetired KB Content Disclaimer
View products that this article applies to.
This article was previously published under Q237188
For a Microsoft Excel 2000 and later version of this article, see 260415  (http://support.microsoft.com/kb/260415/EN-US/ ) .
For a Microsoft Excel 98 version of this article, see 179029  (http://support.microsoft.com/kb/179029/EN-US/ ) .

On This Page

Expand all | Collapse all

SYMPTOMS

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

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.
Back to the top

WORKAROUND

To work around this problem, use a combination of the SUM and IF functions together in an array formula.
Back to the top

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))
Back to the top

STATUS

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

MORE INFORMATION

The SUMIF function uses the following syntax:
=SUMIF(range, criteria, sum_range).
Back to the top

REFERENCES

For more information about a wizard that can help you create these functions, click the Office Assistant, type conditional sum wizard, click Search, and then click to view "Summarize values in a list that meet specific conditions by using the Conditional Sum Wizard."

For more information about array formulas, click the Office Assistant, type array formulas, click Search, and then click to view "About array formulas and how to enter them."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Help is not installed on your computer, click the article number below to view the article in the Microsoft Knowledge Base:
120802  (http://support.microsoft.com/kb/120802/EN-US/ ) Office: How to Add/Remove a Single Office Program or Component
Back to the top
APPLIES TO
  • Microsoft Excel 97 Standard Edition
Back to the top
Keywords: 
kbprb KB237188
Back to the top
Retired KB ArticleRetired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Back to the top
 

Get Help Now

Contact a support professional by E-mail, Online, or Phone

Article Translations