Summary

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.

Generic formula

=AVERAGEIFS(values,range1,criteria1,range2,criteria2)

Explanation 

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")
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.