Average with multiple criteria

In this example, the goal is to calculate an average for each group and region in the data as shown in the worksheet. For convenience, data is an Excel Table in the range B5:D16. This problem can be easily solved with the AVERAGEIFS function. Like the COUNTIFS function and SUMIFS function, the AVERAGEIFS function is designed to accept multiple criteria entered in [range, criteria] pairs. As long as this information is supplied correctly, the behavior of AVERAGEIFS is fully automatic.

AVERAGEIFS function

The AVERAGEIFS function calculates the average of cells in a range that meet one or more conditions, referred to as criteria. The generic syntax for AVERAGEIFS looks like this:

=AVERAGEIFS(avg_range,range1,criteria1,range2,criteria2,...)

In this problem, we need to configure AVERAGEIFS to average sales with two criteria:  (1) group and (2) region. We start off with the average range, which contains the values to average in column D:

=AVERAGEIFS(data[Sales],

Next, we need to enter the criteria needed to target the group. The criteria range is data[Group]. For the criteria, since we already have group names in column F, we will pick up those values directly with a reference to F5:

=AVERAGEIFS(data[Sales],data[Group],F5,

If we entered this formula as-is, it would calculate an average for group "A", ignoring regions. Next, we need to enter the criteria needed to target the regions. In this case, the criteria range is data[Region] and the criteria itself comes from cell G5:

=AVERAGEIFS(data[Sales],data[Group],F5,data[Region],G5)

This is the final formula entered in cell H5. As the formula is copied down, it calculates an average for each group and region in the summary table using the values in columns F and G for criteria.