In SUMIFS function I am getting #VALUE error

Question

For the top table above, I am using the following SUMIFS function:

=SUMIFS($C$3:$G$23,$A$3:$A$23,"=Cinthol",$B$3:$B$23,"=Bangalore")  

to try to get the results in the output format shown, based on two criteria {1. Product and 2. City}. But I am getting #VALUE! error.

Am I doing something wrong?

Answer 1

You have two issues. "each criteria_range argument must contain the same number of rows and columns as the sum_range argument" and your criteria are =Cinthol and =Bangalore.

This may directly be addressed quite easily by repeating the SUMIFS function for each of ColumnsC:G individually (and you might then sum the results).

An alternative would be to add each row up in your data set and apply a PivotTable.