Article ID: 260415 - Last Review: February 2, 2007 - Revision: 4.4

SUMIF, COUNTIF, COUNTBLANK Return #VALUE! Error

View products that this article applies to.
This article was previously published under Q260415
For a Microsoft Excel 98 version of this article, see 179029  (http://support.microsoft.com/kb/179029/ ) .
For a Microsoft Excel 97 version of this article, see 237188  (http://support.microsoft.com/kb/237188/ ) .

On This Page

Expand all | Collapse all

SYMPTOMS

A formula that contains the SUMIF, COUNTIF, or COUNTBLANK functions may return the #VALUE! error in Microsoft Excel.

Note This behavior also applies to the Dfunctions, such as DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, and DVARP.
Back to the top

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

WORKAROUND

To work around this behavior, 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))
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
275165  (http://support.microsoft.com/kb/275165/ ) When to use a SUM(IF()) array formula
Back to the top

STATUS

This behavior is by design.
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 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.
Back to the top
APPLIES TO
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
Back to the top
Keywords: 
kbbug kbpending KB260415
Back to the top
 

Get Help Now

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

Article Translations

 

Related Support Centers