# SUMIF, COUNTIF, and COUNTBLANK functions return "#VALUE!" Error

## Symptoms

A formula that contains the SUMIF, SUMIFS, COUNTIF, COUNTIFS, 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. OFFSET and INDIRECT functions also have this behavior.

## Cause

This behavior occurs when the formula that contains the function refers to cells in a closed workbook and the cells are calculated.

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.

### Examples

Note

You must enter each formula as an array formula. To enter 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))

When to use a SUM(IF()) array formula, use a logical AND or OR to replace the SUMIFS or COUNTIFS function.

## Status

This behavior is by design.