Excel AVERAGEIF Function

This tutorial explains how to use the Excel AVERAGEIF function.

Description

The Excel AVERAGEIF function calculate the average of all the cells in a range that meet single criteria.

There are other AVERAGES functions that you can use to calculate the average of a group of numbers (AVERAGE, AVERAGEA, and AVERAGEIFS).

  • Use the Excel AVERAGE function if you want to calculate the average of a group of numbers.
  • Use the Excel AVERAGEIFS function if you want to calculate the average of only the values that meet the multiple criteria.
  • Use the Excel AVERAGEA function if you want to include logical values and text representations of numbers in a reference as part of the calculation.

Syntax
=AVERAGEIF (range, criteria, [average_range])
Arguments

  • range: A range of cells.
  • criteria:  The criteria that defines which cells are averaged.
  • average_range: [Optional]. A range of cells to average.

range can numbers, ranges, names, arrays, or cell references.

criteria can be a number, cell reference, expression, or text.

If average_range is omitted, range is a range of cells to average.

AVERAGEIF ignores the empty cells in range and average_range.

AVERAGEIF ignores the cells in range that contain TRUE or FALSE.

AVERAGEIF returns the error value (#DIV/0!) if there is no cells in the range meet the criteria.

AVERAGEIF allows the wildcard characters (*) and (?) in criteria.

average_range does not have to be the same size and shape as range.

Example

Here is our sample data.

A B C D E F G
1 Date Code Item Name Quantity Price ($) Amount ($) Employee
2 5/10/2017 Item01 Sony Desktop PC 2 225 450 James
3 5/10/2017 Item02 Dell Desktop PC 2 750 1500 James
4 5/10/2017 Item01  Sony Desktop PC 1 235  235 James
5 5/10/2017 Item03 HP Desktop PC 3 785  2355  Roland
6 5/11/2017 Item02 Dell Desktop PC 3 1050  3150  Roland
7 5/12/2017 Item01 Sony Desktop PC 1 275 275  James
8 5/12/2017 Item03 HP Desktop PC 4 290  1160  Roland

Example 1:

=AVERAGEIF(F2:F8, “>1000”)

In Example 1, we want to get the average of cells that sales amount greater than 1000 in the range F2:F8 . As you can see, there only two arguments in the formula and average_range is omitted. Thus, AVERAGE computes the average of cells in range. The output of the AVERAGE formula is 2041.25.

There is an only argument (F2:F8) in the AVERAGE formula above. AVERAGE returns the average of the numbers in the range F2:F8. The result is 741.6666667.

Example 2:
If we want to calculate the average of sales amount by an employee, we can do as the following formula.

=AVERAGEIF(G2:G8,“Roland”,F2:F8)

In Example 2, the AVERAGEIF function calculates the average of the sales amount by Roland in column F . The AVERAGEIF returns 2221.66666666667.

Example 3:
Let’s another example below.
If we want to calculate the average of the sales amount by employee name that starts with J, we can use the formula below. Here we need to use the wildcard character (*).

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

In Example 3, The AVERAGEIF function calculates the average of the sales amount by employee name that starts with J. As you can see, we use * after J to match any employee names that starts with J. The result is 615.

Example 4:
Lest’s another example that uses (?) in criteria.

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

In Example 4, the AVERAGEIF calculates the average of the sales amount by all employees that name starts with Rola and ends with any two characters. The result is 2221.66666666667.

In this tutorial, you learned how to use the Excel AVERAGEIF function. We use the Excel AVERAGEIF function to calculate the average of cells in a range that meet single criteria.

Here is a full list of Microsoft Excel functions.