Exceljet

Quick, clean, and to the point

Excel AVERAGEIF Function

Excel AVERAGEIF function
Summary 

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.

Purpose 
Get the average of numbers that meet criteria.
Return value 
A number representing the average.
Syntax 
=AVERAGEIF (range, criteria, [average_range])
Arguments 
  • 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.
Usage notes 

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:

=AVERAGEIF(C5:C15,">0") // price greater than $0
=AVERAGEIF(C5:C15,">200000") // price greater than $200k
=AVERAGEIF(D5:D15,">=2",C5:C15) // 2+ bedrooms
=AVERAGEIF(D5:D15,">=3",C5:C15) // 3+ bedrooms

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.

=AVERAGEIF(D5:D15,2,C5:C15) // 2 bedrooms
=AVERAGEIF(D5:D15,">=2",C5:C15) // 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:

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