SUMIFS Value Error

When you use SUMIFS in a formula you may discover that your get a SUMIFS value error. Normally a value error means that one of the references is not a value but in this case the reason is different and more importantly it acts differently to the normal SUMIF.

You will see in the image below that the SUMIFS (cell C8) has generated a #VALUE! error. This is due to the Sum range and the Criteria range not matching i.e. the sum range is C24 to D48 (2 columns and 24 rows) but the criteria range is B24 to B48 (1 column and 24 rows).

SUMIFS Value Error

With a SUMIFS this results in a #VALUE! error. Interestingly, if you make the same mistake with a normal SUMIF you get no error, it does a calculation. So in the example above (cell D8), although the SUMIF also refers to 2 columns and 24 rows versus 1 column and 24 rows it generates an answer.

In conclusion, when using a SUMIFS (and a SUMIF), make sure that the Sum Range and Criteria Range are matching sizes and shapes.

Related: Learn more about SUMIFS/ COUNTIFS/ AVERAGEIFS in the Advanced Excel Course