How to Use AVERAGEIFS Function

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.

Averageifs Function

Leave a Reply