It returns the arithmetic mean (average) of all the cells in a given range that meet a specific criterion.
The syntax of the function is the following:
AVERAGEIF(range, criterion, [average_range])
range: The cells or range of cells we want to calculate the average for, unless the average_range argument is defined. If the optional attribute average_range is defined then range is the range to which we apply the criterion.
criterion: It is the criterion that defines which cells of the range should be included in the calculation of the average.
[average_range]: Optional. The actual set of cells to average. If this argument is omitted the range attribute is used.
If the cells in range are empty or contain logical or text values they are ignored.
If range is blank the function throws the #DIV/0! error.
If no cells meet the criterion the function throws the #DIV/0! error.
If a criterion cell is empty, it is treated as a zero.
You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
Click on the button to practice using this function, with the help of our Online Assessment Tool:
Here is an example of how to use the AVERAGEIF function:
At the cell area B8:G8 of Sheet3 show the average grade of the lessons with grades greater than or equal to 5.