AVERAGEIFS
AVERAGEIFS “returns the average (arithmetic mean) of all cells that meet multiple criteria”
- Microsoft Office Support 2019.
The difference between AVERAGEIF and AVERAGEIFS is the number of criteria that you would like the cells to meet. AVERAGEIF meet a criterion, AVERAGEIFS meets multiple criteria.
Excel Formula: =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, [criteria2],...)
Note: any component of the formula that is in square brackets means it’s optional.
Formula Made Easy
Example
In my "Cars" data, I want to work out the AVERAGE AGE of MERCEDES-BENZ cars (criteria1) that are SILVER in colour (criteria2) .
Based on the above photo, if I were to fill in the cell references into my Formula Made Easy table, it will look like:
Formula Applied
Putting the above references into the Excel formula, it will be:
=AVERAGEIFS(E:E,A:A,”Mercedes-Benz",B:B,"Silver")
Comments