Exceljet

Quick, clean, and to the point

Average with multiple criteria

Excel formula: Average with multiple criteria
Generic formula 
=AVERAGEIFS(values,range1,criteria1,range2,criteria2)
Explanation 

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.

How this formula works

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")
Author 
Dave Bruns

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.