AVERAGEIF Function

AVERAGEIF is a combination of the AVERAGE function and a conditional component. AVERAGEIF returns the average (or arithmetic mean) of all the cells in a range that meet a specified condition.

Syntax

AVERAGEIF(range, criteria, [average_range])

Arguments

Argument	Description
Range	Required. A reference to one or more cells to average. This argument can include numbers, cell references, or named ranges.
Criteria	Required. This is a logical test that determines which cells are included in the average .
Average_range	Optional. The actual set of cells to average if not the cells in the range argument. If this argument is omitted, range is used.

Notes:

• Cells in range that contain logical values like TRUE or FALSE are ignored. 
• AVERAGEIF will return an error (#DIV0!) if range is a blank or text value. 
• If a cell in criteria is empty it is treated as a zero (0) value. 
• AVERAGEIF returns the #DIV/0! error value if no cells in the range meet the criteria. 
• Average_range does not necessarily need to be the same number of rows and columns as range. The cells that are averaged are determined by using the top-left cell in average_range as the first cell and then including cells that match the same number of rows and columns in range. See the examples below:

• If the range is A1:A10 and average_range is B1:B10, then the actual cells evaluated would be B1:B10.
• If range is A1:A10 and average_range is B1:B5, then the actual cells evaluated would be B1:B10.
• If range is A1:B5 and average_range is C1:C3, then the actual cells evaluated would be C1:D5.

Example

In the following example, we use the AVERAGEIF function to calculate the average exam scores for students per subject. We want to group the data by Subject (for example, Biology, Chemistry, Maths etc.) and average each group by Score .

The range we will be using to select the data - B2:B16, is different from the range we want to actually average - C2:C16.

Formula explanation:

=AVERAGEIF($B$2:$B$16,E2,$C$2:$C$16)

• The Range argument references the Subject column B2:B16 (this has been set to absolute reference - $B$2:$B$16 ).
• The Criteria argument is E2 . This is a reference to the subjects we want to use as our criteria. Instead of directly entering this value into the formula, a cell reference has been used to make it easier to change. This argument is a relative reference (the default) because we want the cell to change relatively as we copy the formula to other cells.
• The Average_range is C2:C16 (which is $C$2:$C$16 as an absolute reference). This is the range for which we want to calculate the average of values that meet our criteria. Use the F4 key to make it an absolute reference.

We enter the formula in cell F2 to return the Maths average. Then the Fill Handle of the cell was used to copy the formula to cells F3:F5 which displays the average for the other subjects.