Excel AVERAGEIFS Function

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. ~?, ~*).