The Excel AVERAGEIFS function calculates the average value from a range of cells with a given condition or multiple conditions. The AVERAGEIFS function is useful for calculating the average of subset of an overall dataset.
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
average_range = the range of cells to calculate the average from if the condition(s) are true
criteria_range1 = the range of data to test the criteria against
criteria1 = the criteria to test against the range of data
Only one criteria range and criteria is required, but additional criteria ranges and criteria can be added if desired.
The AVERAGEIFS formula returns the mathematical average of the average_range for the cells that meet the condition(s) defined by the criteria.
In the example above the AVERAGEIFS formula in cell N7 calculates average of the range of cells between I6:I35, which is the average age of the college applicants in the example dataset, when the applicant is from the country US, as defined by the cell reference M7.
The resulting average of US applicants is returned in cell N7, 25.9 years.