Average with multiple criteria

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")