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 criteria. The criteria used for AVERAGEIFS can include logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

Purpose 
Average cells that match multiple criteria
Return value 
The average 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 calculates the average of the numbers in a range that meet supplied 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 – all criteria must be true for a number to be included in the average.

Criteria can be supplied as numbers, strings, or references. For example, valid criteria could be 10, ">10", A1, or "<"&A1. Criteria can also include wildcards for partial matching.

AVERAGEIFS is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and AVERAGEIFS requires a cell range for range arguments – you can't use an array.

AVERAGEIFS will automatically ignore empty cells, even when criteria match. In other words, AVERAGEIFS will not include empty cells in the average. AVERAGEIFS returns #DIV/0! if no cells in meet criteria.

Examples

In the example shown, the formulas in H5:H7 are:

=AVERAGEIFS(C5:C15,C5:C15,">0")
=AVERAGEIFS(C5:C15,C5:C15,">0",C5:C15,"<500000")
=AVERAGEIFS(C5:C15,D5:D15,">=2",E5:E15,">1")

These formulas return the average price of properties where:

  1. prices are greater than zero
  2. prices are greater than zero and less than $500,000
  3. properties have at least 2 bedrooms and more than 1 bathroom

Double quotes ("") in criteria

In general, text values in criteria are enclosed in double quotes (""), and numbers are not. However, when a logical operator is included with a number, the number and operator must be enclosed in quotes. Note the difference in the two examples below. Because the second formula uses the greater than or equal to operator (>=), the operator and number are both enclosed in double quotes.

=AVERAGEIFS(C5:C15,D5:D15,2) // 2 bedrooms
=AVERAGEIFS(C5:C15,D5:D15,">=2") // 2+ bedrooms

Double quotes are also used for text values. For example, to average values in B1:B10 when values in A1:A10 equal "red", you can use a formula like this:

=AVERAGEIFS(B1:B10,A1:A10,"red")

Multiple criteria

Enter criteria in pairs [range, criteria]. For example, to average values in A1:A10, where B1:B10 = "A", and C1:C10 > 5, use:

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

Value from another cell

A value from another cell can be included in criteria using concatenation. In the example below, AVERAGEIFS will return the average of numbers in A1:A10 that are less than the value in cell B1. Notice the less than operator (which is text) is enclosed in quotes.

=AVERAGEIFS(A1:A10,A1:A10,"<"&B1) // average values less than  B1

Wildcards

The wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in criteria. A question mark (?) matches any one character and an asterisk (*) matches zero or more characters of any kind.  For example, to average values in B1:B10 when values in A1:A10 contain the text "red", you can use a formula like this:

=AVERAGEIFS(B1:B10,A1:A10,"*red*")

The tilde (~) is an escape character to allow you to find literal wildcards. For example, to match a literal question mark (?), asterisk(*), or tilde (~), add a tilde in front of the wildcard (i.e. ~?, ~*, ~~).

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

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 average_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 zero or more characters of any kind.
  • To find a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).