# 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.

### 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:

- 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.

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

## Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.