# Excel AVERAGEIF Function

The Excel AVERAGEIF function calculates the average of numbers in a range that meet supplied criteria. AVERAGEIF criteria can include logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

**range**- One or more cells, including numbers or names, arrays, or references.**criteria**- A number, expression, cell reference, or text.**average_range**- [optional] The cells to average. When omitted, range is used.

AVERAGEIF calculates the average of the numbers in a range that meet supplied criteria. Criteria can be supplied as numbers, strings, or references. For example, valid criteria could be 10, ">10", A1, or "<"&A1.

The *average_range* argument is optional. When *average_range* is not provided, AVERAGEIF if will calculate the average of numeric values in the *range* argument. When *average_range* is provided, AVERAGEIF will calculate the average of numbers in *average_range*.

AVERAGEIF criteria can include logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

AVERAGEIF 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 AVERAGEIF *requires* a cell range for range arguments, you can't use an array.

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

The AVERAGEIF function can only apply a single condition. If you need to apply multiple criteria, use the AVERAGEIFS function.

### Examples

In the example shown the formulas in H5:H8 are as follows:

### Double quotes ("") in criteria

In general, text values 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:

=AVERAGEIF(A1:A10,"red",B1:B10) // average "red" only

### Value from another cell

A value from another cell can be included in criteria using concatenation. In the example below, AVERAGEIF 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.

=AVERAGEIF(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 cells in a B1:B10 when cells in A1:A10 contain the text "red" anywhere, you can use a formula like this:

=AVERAGEIF(A1:A10,"*red*",B1:B10) // contains "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. ~?, ~*, ~~).

### Notes

- Cells in
*range*that contain TRUE or FALSE are ignored. - Empty cells are ignored in
*range*and*average_range*when calculating averages. - AVERAGEIF returns #DIV/0! if no cells in range meet criteria.
*Average_range*does not have to be the same size as*range*. The top left cell in*average_range*is used as the starting point, and cells that correspond to cells in*range*are averaged.- AVERAGEIF allows the wildcard characters question mark (?) and asterisk (*), in criteria. The ? matches any single character and the * matches any sequence of characters. To find a literal ? or *, use a tilde (~) before the character, i.e. ~* and ~?.

## Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.