How to correct a #VALUE error in the SUMIF/SUMIFS function

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
In the SUMIFs function, the criteria_range must be consistent with sum_range

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?

Post a question in the Excel community forum

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.

See Also

Correct a #VALUE! error

SUMIF function

SUMIFS function

Advanced IF function videos

Overview of formulas in Excel

How to avoid broken formulas

Use error checking to detect errors in formulas

All Excel functions (alphabetical)

All Excel functions (by category)

Connect with an expert
Contact us
Expand your skills
Explore training

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.