Excel AVERAGEIFS Function
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.
- 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.
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 meet criteria.
In the example shown, the formulas in H5:H7 are:
These formulas return the average price of properties where:
- prices are greater than zero
- prices are greater than zero and less than $500,000
- 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.
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:
Enter criteria in pairs [range, criteria]. For example, to average values in A1:A10, where B1:B10 = "A", and C1:C10 > 5, use:
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
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:
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.
- 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. ~?, ~*).