## Summary

To average numbers based on multiple criteria, you can use the AVERAGEIFS function. In the example shown, the formula in H5 is:

``````=AVERAGEIFS(sales,group,F5,region,G5)
``````

where data is an Excel Table in the range B5:D16. 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.

## Generic formula

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

## Explanation

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.

Author

### 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.