# Average with multiple criteria

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

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.

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

## Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

## Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.