SUMIFS function is a little bit different from SUMIF function in that you can use it to get summation output/result from multiple criteria unlike SUMIF function that can only to get the summation based on a particular condition or criteria.
The table below will help you to comprehend the difference between the two functions and will help you to know how to use SUMIFS function
The table shows the quantity sold of a particular product by a particular seller
The table contain serial number from 1 – 6
We are going to be using SUMIFS to know how many Laptop sold ( i.e Qty Sold) by Clinton
Steps on how to use SUMIFS function in Excel
- Put your cursor at the cell below the Qty Sold i.e cell E11 and press the SUMIFS Funtion
=SUMIFS(sum_range, criteria_range1, critera1, criteria_range2, critera2)
Sum_range is the actual range of cell to sum in this case it is qty column .
Criteria_range1 is the first criteria range which is the Product range from cell C2 – C7. In excel anytime you see range it means that it’s all the cells in a particular column or row (Multiple cells)
Criteria 1 is a single cell within the criteria_range i.e the criteria1 cell must be one of the cells in the criteria range.
In this case we selected all the cells in the product column as the criteria_range1, therefore the criteria1 which is Laptop is found within the product column
Criteria_range2 is all the cells in the seller column which is D2-D7.
Criteria2 is the Clinton cell which is one of the cells in the seller column.
2. After you have typed the function as explained above you will be able to get 53 as the total Laptops sold by Clinton. You can change the seller and the product to get another value.
Note: You can use list data validation to bring in drop down menu to C11 and D11 so that all the sellers and products will show as combo box when the cell C11 and D11 is clicked and the Qty sold base on the two criteria will be update based on the product and seller clicked.
I believed with this explanation, you now understand how to use SUMIFS function in Excel
