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:
To restrict calculation by group we provide:
To restrict calculation by region we use:
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: