Exceljet

Quick, clean, and to the point

Excel AVERAGEIFS Function

Excel AVERAGEIFS function
Summary 

The Excel AVERAGEIFS function calculates the average of numbers in a range that meet one or more supplied criteria. The criteria in AVERAGEIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

Purpose 
Average cells that match multiple criteria
Return value 
The aveage of the cells that meet all criteria
Syntax 
=AVERAGEIFS (avg_rng, range1, criteria1, [range2], [criteria2], ...)
Arguments 
  • avg_rng - The range to average.
  • range1 - The first range to evaulate.
  • criteria1 - The criteria to use on range1.
  • range2 - [optional] The second range to evaluate.
  • criteria2 - [optional] The criteria to use on range2.
Usage notes 

The AVERAGEIFS function can calculate an average based on more than one criteria. The first range given to AVERAGEIFS is the range containing numbers to average. Criteria are supplied in pairs [range, criteria] and only the first pair is required. For each additional criteria, supply another range/criteria pair. The criteria given to AVERAGEIFS are applied with AND logic. Data must meet all criteria to be included in the average. Criteria can include wildcards.

Examples

To average values in A1:A10, where B1:B10 = "A", and C1:C10 > 5, you can use:

=AVERAGEIFS(A1:A10,B1:B10,"A",C1:C10,">5")

In the example shown, the formulas in H6:H8 are:

=AVERAGEIFS(prices,prices,">0",prices,"<500000")
=AVERAGEIFS(prices,beds,">=2",baths,">1")
=AVERAGEIFS(prices,prices,">0")

where "prices" (C6:C15), "beds" (D6:D15), and "baths" (E6:E15) are named ranges.

Note: the order of arguments is different between the AVERAGEIFS and AVERAGEIF functions. The range to average is the first argument in AVERAGEIFS, but the third argument in AVERAGEIF.

Notes

  • If no data matches criteria, AVERAGEIFS returns the #DIV0! error 
  • Each additional range must have the same number of rows and columns as the avg_range.
  • Non-numeric criteria needs to be enclosed in double quotes but numeric criteria does not. For example: 100, "100", ">32", "jim", or A1 (where A1 contains a number).
  • The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters.
  • To find a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).

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.