Author: Oscar Cronquist Article last updated on March 27, 2018

The AVERAGEIF function returns the average of cell values that are valid for a given condition.

Formula in cell D3:

=AVERAGEIF(B3:B7,E3,C3:C7)

Excel Function Syntax

AVERAGEIF(range, criteria, [average_range])

Arguments

range Required. One or more cells to average, including numbers or names, arrays, or references that contain numbers.
criteria Required. A cell reference, expression or text that determines which values to be averaged.
average_range Optional. Cells to average. If not entered, range is used.

Comments

Boolean values TRUE or FALSE are excluded in the calculation.

An empty cell in argument average_range is excluded.

A blank or text value in argument range is not valid, the function returns #DIV0!.

If a condition in argument average_range is empty is the same as 0 (zero).

The AVERAGEIF function returns #DIV0! if no values match the criteria.

It is possible to use wildcard characters like (?) question mark or (*) asterisk.

(?) question mark matches any single character.

(*) asterisk matches any sequence of characters.

Use a tilde (~) before to escape the wildcard characters meaning finding the actual question mark or asterisk.