Syntax
The generic syntax for AVERAGEIF looks like this: The AVERAGEIF function takes three arguments: range, criteria, and average_range. Range is the range of cells to apply a condition to. Criteria is the condition to apply, along with any logical operators that are needed. Average_range argument is optional. When average_range is not provided, AVERAGEIF will average values in the range argument. When average_range is provided, AVERAGEIF will average values in average_range. When using AVERAGEIF, keep the following in mind:
AVERAGEIF will not include empty cells in the average, even when criteria match. AVERAGEIF will return a #DIV/0! error if no cells meet criteria. Criteria can include logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. AVERAGEIF can only apply one criteria. To use multiple criteria, see the AVERAGEIFS function.
Criteria
The AVERAGEIF function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Because AVERAGEIF is in a group of eight functions that split logical criteria into two parts, the syntax is a bit tricky. Range and criteria are provided separately, and operators in criteria need to be enclosed in double quotes (""). The table below shows some common examples: Notice the last two examples use concatenation with the ampersand (&) character. When a criteria argument includes a value from another cell, or the result of a formula, logical operators like “<” must be joined with concatenation. This is because Excel needs to evaluate cell references and formulas first to get a value, before that value can be joined to an operator.
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:
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.
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: 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. ~?, ~, ~~).
Average range caution
AVERAGEIF makes certain assumptions about the size of average_range, essentially resizing it when necessary to match the range argument, using the upper left cell in the range as an origin. In some cases, this behavior can create a result that seems reasonable but is in fact incorrect. For an example of this problem, see this article.
Notes
TRUE and FALSE values ignored when calculating an average. Empty cells are ignored when calculating an average. AVERAGEIF returns #DIV/0! if no cells in range meet criteria. AVERAGEIF requires a range, you can’t substitute an array. 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 supports wildcards but is not case-sensitive.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
