Excel AVERAGEIF Function with Examples

How to Use the Excel AVERAGEIF Function

In this tutorial, you'll learn how to use the Excel AVERAGEIF function.

AVERAGEIF is a function to calculate the average of a group of numbers in a range of cells that meet single criteria.

AVERAGEIF Syntax

The syntax of the AVERAGEIF() function is as follows:

=AVERAGEIF (range, criteria, [average_range])

Arguments

• range: A range of cells
• criteria: The criteria that define which cells to average
• average_range: Optional. A range of cells to average. When omitted, range is a range of cells to average.

Remarks

• range can numbers, ranges, names, arrays, or cell references.
• criteria can be a number, cell reference, expression, or text.
• AVERAGEIF() ignores the empty cells in average_range. 
• Cells that contain TRUE or FALSE are ignored.
• AVERAGEIF() returns the error value (#DIV/0!) if there is no cells in the range meet the criteria.
• The size and shape of average_range and range don't have to be the same.
• AVERAGEIF() allows the wildcard characters (*) and (?) in criteria.

AVERAGEIF Function Examples

Demo data:

Example 1

The following example calculates the average of cells that the amount is greater than 1000 in the range F2: F8:

=AVERAGEIF(F2:F8, ">1000")

Here, the average_range argument is omitted. the range argument is a range of cells to average.

The above formula returns 3066.667.

Example 2

The following example calculates the average of all amounts by the employee named Roland:

=AVERAGEIF(G2:G8,"Roland",F2:F8)

The range G2: G8 is a criteria range and the range F2: F8 is a range of cells to average.
The above formula returns 2366.667.

Example 3

You can perform a partial matching in criteria, as the following example shows:

=AVERAGEIF(G2:G8,"J*",F2:F8)

The above example calculates the average of all amounts of the employee that name begins with a letter J and ends with any text or string.

The above formula returns 997.5, which is the average of all amounts by the employee James.

Example 4

Here is another example of partial matching in criteria:

=AVERAGEIF(G2:G8,"Rola??",F2:F8)

In the above formula, the criteria "Rola??" matches any employee that name begins with Rola and ends with any two characters. 

As you can see in the above demo data, the employee named Roland matches this case.

The above formula returns 2366.667.

In this tutorial, you've learned how to use the Excel AVERAGEIF function. 

AVERAGEIF is a function to calculate the average of a group of numbers in a range that meets a criteria.

There are other AVERAGE functions as described below.

• If you want to calculate the average of a group of numbers, use  AVERAGE(). 
• If you want to calculate the average of only the values that meet the multiple criteria, use AVERAGEIFS(). 
• If you want to include logical values and text representations of numbers in a reference as part of the calculation, use AVERAGEA().