This topic lists the more common causes of the #VALUE! error in the SUMIF and SUMIFS functions and how to resolve them.

## Problem: The formula refers to cells in a closed workbook

SUMIF/SUMIFS functions that refer to a cell or a range in a closed workbook will result in a #VALUE! error.

**Note:** This is a known issue with several other Excel functions such as COUNTIF, COUNTIFS, COUNTBLANK, to name a few. See the SUMIF, COUNTIF and COUNTBLANK functions return "#VALUE!" Error article for more information.

**Solution:** Open the workbook indicated in the formula, and press F9 to refresh the formula.

You can also work around this issue by using SUM and IF functions together in an array formula. See the SUMIF, COUNTIF and COUNTBLANK functions return #VALUE! error article for more information.

## Problem: The criteria string is more than 255 characters

The SUMIF/SUMIFS functions returns incorrect results when you try to match strings longer than 255 characters.

**Solution:** Shorten the string if possible. If you can’t shorten it, use the CONCATENATE function or the Ampersand (&) operator to break down the value into multiple strings. For example:

=SUMIF(B2:B12,"long string"&"another long string")

## Problem: In SUMIFS, the *criteria_range* argument is not consistent with the *sum_range* argument.

The range arguments must always be the same in SUMIFS. That means the *criteria_range* and *sum_range* arguments should refer to the same number of rows and columns.

In the following example, the formula is supposed to return the sum of daily sales of Apples in Bellevue. The *sum_range (C2:C10)* argument however does not correspond to the same number of rows and columns in the *criteria_range (A2:A12 & B2:B12)* arguments. Using the syntax =SUMIFS(**C2:C10**,**A2:A12**,A14,**B2:B12**,B14) will result in the #VALUE! error.

**Solution:** Following this example, change the *sum_range* to C2:C12 and retry the formula.

**Note:** SUMIF can use different size ranges.

## Do you have a specific function question?

## Help us improve Excel

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice.