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.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
