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.

Incorrect formula is =SUMIFS(C2:C10,A2:A12,A14,B2:B12,B14), where C2:C10 needs to be C2:C12

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

Note: SUMIF can use different size ranges.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.

See Also

Correct a #VALUE! error

SUMIF function

SUMIFS function

Advanced IF function videos

Overview of formulas in Excel

How to avoid broken formulas

Detect errors in formulas

All Excel functions (alphabetical)

All Excel functions (by category)

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.