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 true value in Excel (perhaps has a space in it or is a word) but in this case the reason is different and more importantly it acts differently to the normal SUMIF.
YouTube #Value errors with SUMIFS
SUMIFS situation with #VALUE errors
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).
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
SUMIFS returns value when linked workbooks are closed