SUMIF Function in Excel Definition with Formula:
The Excel SUMIF returns a sum of values within a range that matches a given condition.
Formula: SUMIF(range, criteria, [sum_range])
SUMIFS Function in Excel Definition with Formula:
The SUMIFS Function in Excel also returns a sum of values within a range that matches a given condition. The only difference is that this function matches the criteria on multiple rows.
Formula: SUMIFS(sum_range, criteria_range1, criteria1, ………..)
Explanation with Example Questions:
Brenda wanted to teach her children the difference between the SUMIF function in Excel and SUMIFS function with a real-life example. She sent carl and Carly to the market to buy some fruits, vegetables, snacks and drinks.
Carl and Carly came home after buying some grocery items. Brenda was happy as some items were at a discount.
To teach the concept, she asked them to tell what snacks, vegetables, drinks and fruits cost separately and create an excel sheet of the items.
To teach them the functioning of SUMIF and SUMIFS in Excel, she asked her children to calculate:
- The total cost of all the fruits.
- The total cost of the drinks available at a discount of more than 20%.
- The total cost of undiscounted fruits.
Carl and Carly being a layman for excel-sheets started calculating the prices using a calculator at first and then somehow remembered yesterday’s class of IF and IFS function in Excel and used that.
Seeing them making efforts, Brenda finally taught them the SUMIF AND SUMIFS in Excel to prepare them for home budgeting using excel.
- SUMIF works on a single column; SUMIFS works on multiple columns.
- sum_range is provided at the end in SUMIF while at the beginning in SUMIFS.
- SUMIFS work on multiple conditions.
Now coming back to the problem of finding the total cost of fruits; cost of fruits available at a discount of more than 3%; undiscounted fruits. Here are the answers :
The total cost of all the fruits
Formula: SUMIF(E4:E10,“Fruits”,H4:H10)
ANSWER = 10
Discount of more than 20%
Formula: SUMIFS(H4:H10,E4:E10,”Drink”,G4:G10,”>20%”)
- H4:H10 – The sum range i.e. the range of whose sum we require and in this case, it’s the net price.
- E4:E10 – The criteria_range1 on which we are applying the condition. Criteria 1 for this range is “Drink”. If the values in the range match “Drink” then the condition is satisfied and will move forward and look for other conditions.
- G4:G10 – The criteria_range2; means the second condition after the success of the first condition. Here the 2nd criterion is more than 20% discount (“>20%”).
Answer: 6
Undiscounted
Formula: SUMIFS(H4:H10,E4:E10,”Fruits”,G4:G10,”=0%”)
- H4:H10 – The sum range means the range of whose sum we require and in this case, it’s the final price.
- E4:E10 – The criteria_range1 on which we are applying the condition. For this the criteria 1 is “Fruits”. If the values in the range match “Fruits” then the condition is satisfied and will move forward and look for other conditions.
- G4:G10 – The criteria_range 2; means the second condition after the success of the first condition. Here the 2nd condition is 0 discount (“=0%”).
Answer: 10
With these examples, Carl and Carly learnt a valuable lesson from their mom on the Excel SUMIF and SUMIFS, which will further help them to learn about the concept of the home budgeting. Maybe, Brenda will also get some help from her kids in managing home budget and doing the taxes.