Average with multiple criteria
=AVERAGEIFS(values,range1,criteria1,range2,criteria2)
To average numbers based on multiple criteria, you can use the AVERAGEIFS function. In the example shown, the formula in I5 is:
=AVERAGEIFS(sales,group,F5,region,G5)
where "group" (B5:B14), "region" (C5:C14), and "sales" (D5:D14) are named ranges.
How this formula works
Like the COUNTIFS and SUMIFS function, the AVERAGEIFS function is designed to handle multiple criteria which are entered in [range, criteria] pairs. As long as this information is supplied correctly, the behavior of AVERAGEIFS is fully automatic.
In the example shown, we have group values in column F and region values in column G. We use these values directly by using cell references for criteria.
The first argument holds the range of values to average:
sales
To restrict calculation by group we provide:
group,F5
To restrict calculation by region we use:
region,G5
The result in cell J5 is 105:
=AVERAGEIFS(sales,group,F5,region,G5) // returns 105
Without named ranges
Named ranges are used for readability only. The equivalent formula in J5, without named ranges and with criteria values hardcoded is:
=AVERAGEIFS(D5:D14,B5:B14,"A",C5:C14,"East")
