In Excel, you can use AVERAGEIFS function when you need to calculate the average of a group of numbers that meet one or more criteria (or AVERAGEIF for only one criterion).
Formula:
= AVERAGEIFS (average range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Explanations:
– The average range is required, which is the range of numbers to average;
– Criteria_range1 is required, which is the first criteria to select from;
– Criteria1 is required, which is the first criteria that need to meet;
– Criteria_range2, criteria2 are optional.
Cautions:
The AVERAGEIFS function will average cells that contain only numbers. When there is only one criterion, please refer to AVEAGEIF function.
Example 1: What is the average salary for those over $70,000 in South Region?
=AVERAGEIFS(E2:E8, D2:D8, "South", E2:E8,">70000")
The average salary is $79,602.5 for those earned over $70,000 in South Region.
Example 2: What is the average salary in South Region for those having the letter "m" in the last name?
= AVERAGEIFS(E2:E8, D2:D8, "South", B2:B8,"*m*")
The average salary in South Region that having letter "m" in the last name is $70,452.
Example 3: What is the average in South Region for those older than C4 ( 10/26/1984)?
=AVERAGEIFS(E2:E8, D2:D8, "South",C2:C8,"<"&C4)
The average salary in South Region for those are older than C4 (10/26/1984) is $70,452.
