SUMIFS and COUNTIFS Functions in Excel – how are these different to the SUMIF and COUNTIF functions?
This week’s hint and tip is on the SUMIFS and COUNTIFS functions in Excel. Functions (also referred to as formulas) are used by many on a day to day basis in Excel. These functions are seen as a ‘step on’ from the SUMIF and COUNTIF as they work off multiple criteria and so can give a bit more flexibility in their use. We cover the SUMIFS formula on Master Class Excel Silver training course, but we decided to do a hint and tip on it with COUNTIFS too. We are going to go through it now below.
SUMIFS function in Excel
The SUMIFS function is the same as the SUMIF except it has a bit more flexibility in that it can add up ranges based off multiple criteria whereas SUMIF only adds up based off one criteria.
The syntax for the formula can be seen below with explanation for each part:
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
The parts you need are:
- sum_range: this is required.
- criteria_range1: this is required. This is the first range for the criteria associated with it
- criteria1: this is required. This is the criteria that defines which cells to be counted in the formula
- [criteria_range2, criteria2]…: these are optional (shown by the square brackets)
Look at the construction of the formula below.
We’ve used the SUMIFS formula to work out how many Oranges were sold by Susan. Here the formula picks up row 3 and row 7 where product is Oranges and the salesperson is Susan. Then it adds up the cells in column A meeting these criteria i.e. 4 + 12 = 16
COUNTIFS function in Excel
Similarly, with the COUNTIFS function, it has a bit more flexibility than COUNTIF in that it can count up based off multiple criteria than just one criteria with COUNTIF.
The syntax for the formula is shown below with an explanation for each part:
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
The parts you need are:
- criteria_range1: this is required. This is the first range for the criteria associated with it
- criteria1: this is required. This is the criteria that defines which cells to be counted in the formula
- [criteria_range2, criteria2]…: these are optional (shown by the square brackets)
Look at the construction of the formula below.
We’ve used the COUNTIFS formula to work out how much Bread was sold by Jane. Here the formula picks up row 3 and row 7 where product is Bread and the salesperson is Jane. Then it counts up how many rows in the data meeting these criteria, i.e. 1 row + 1 row = 2 rows
The video below talks through the SUMIFS and COUNTIFS functions and how they can be used in Excel.
Take a look below at the video to find out more and then try them out on your own computer!
We hope you have enjoyed this hint and tip on the SUMIFS and COUNTIFS functions in Excel. Why not take a look at our previous video hint and tip on the autocomplete data validation feature in Excel?