Excel AVERAGEIF: Find the Average for Specific Criteria

Screenshot/Microsoft

The AVERAGEIF function combines the IF function and AVERAGE function in Excel. This combination allows you to find the average or arithmetic mean of those values in a selected range of data that meets specific criteria.

The IF portion of the function determines what data meets the specified criteria and the AVERAGE part calculates the average or mean.

Normally, AVERAGE IF is used with rows of data called records. In a record, all of the data in each cell in the row is related – such as a company's name, address and phone number.

AVERAGE IF looks for the specific criteria in one cell or field in the record and, if it finds a match, it averages that data or data in another specified field in the same record.

How the AVERAGEIF Function Works

Excel AVERAGE IF Function
Excel AVERAGE IF Function. © Ted French

This tutorial uses the AVERAGE IF function to find the average yearly sales for the East sales region in a set of data records.

Following the steps in the tutorial topics below walks you through creating and using the AVERAGE IF function seen in the image above to calculate average yearly sales.

Tutorial Topics

  • Entering the Tutorial Data
  • The AVERAGE IF Function's Syntax
  • Starting the AVERAGE IF Function
  • Entering the Range Argument
  • Entering the Criteria Argument
  • Entering the Average_range Argument
  • And the Answer is ..

Entering the Tutorial Data

Excel AVERAGE IF Function
Excel AVERAGE IF Function. © Ted French

The first step to using the AVERAGE IF function in Excel is to enter the data.

Enter the data into cells C1 to E11 of an Excel worksheet as seen in the image above.

Note: The tutorial instructions do not include formatting steps for the worksheet.

This will not interfere with completing the tutorial. Your worksheet will look different than the example shown, but the AVERAGE IF function will give you the same results.

Information on formatting options similar to those seen above is available in this Basic Excel Formatting Tutorial.

The AVERAGEIF Function's Syntax

Syntax for the Excel AVERAGEIF Function
Syntax for the Excel AVERAGEIF Function. © Ted French

In Excel, a function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for AVERAGEIF is:

=AVERAGEIF ( Range, Criteria, Average_range )

The AVERAGEIF Function's Arguments

The function's arguments tell the function what condition is being tested for and what range of data to average when that condition is met.

Range – the group of cells the function is to search.

Criteria – this value is compared with the data in the Range. If a match is found then the corresponding data in the Average_range is averaged. Actual data or the cell reference to the data can be entered for this argument.

Average_range (optional) – the data in this range of cells is averaged when matches are found between the Range and Criteria arguments. If the Average_range argument is omitted, the data matched in the Range argument is averaged instead.

Starting the AVERAGEIF Function

Opening the AVERAGE IF Function dialog box
Opening the AVERAGE IF Function dialog box. © Ted French

Although it is possible to just type the AVERAGE IF function into a cell, many people find it easier to use the function's dialog box to add the function to a worksheet.

Tutorial Steps

  1. Click on cell E12 to make it the active cell. This is where we will enter the AVERAGE IF function.
  2. Click on the Formulas tab of the ribbon.
  3. Choose More Functions > Statistical from the ribbon to open the function drop down list.
  4. Click on AVERAGE IF in the list to bring up the AVERAGE IF function's dialog box.

The data that we enter into the three blank rows in the dialog box will form the arguments of the AVERAGE IF function.

These arguments tell the function what condition we are testing for and what range of data to average when the condition is met.

Entering the Range Argument

Entering the Range Argument
Entering the Range Argument. © Ted French

In this tutorial we are looking to find out the average yearly sales for the East sales region.

The Range argument tells the AVERAGE IF function which group of cells to search when trying to find the specified criteria –​ East.

Tutorial Steps

  1. In the dialog box, click on the Range line.
  2. Highlight cells C3 to C9 in the worksheet to enter these cell references as the range to be searched by the function.

Entering the Criteria Argument

Entering the Criteria Argument
Entering the Criteria Argument. © Ted French

In this example if data in the range C3:C12 equals East then the total sales for that record is to be averaged by the function.

Although actual data – such as the word East can be entered into the dialog box for this argument it is usually best to add the data to a cell in the worksheet and then enter that cell reference into the dialog box.

Tutorial Steps

  1. Click on the Criteria line in the dialog box.
  2. Click on cell D12 to enter that cell reference. The function will search the range selected in the previous step for data that matches this criteria.
  3. The search term (East) will be added to cell D12 in the last step of the tutorial.

Cell References Increase Function Versatility

If a cell reference, such as D12, is entered as the Criteria Argument, the AVERAGE IF function will look for matches to whatever data has been typed into that cell in the worksheet.

So after finding the average sales for the East region it will be easy to find the average sales for another sales region simply by changing East to North or West. The function will automatically update and display the new result.

Entering the Average_range Argument

Entering the Average_range Argument
Entering the Average_range Argument. © Ted French

The Average_range argument is the group of cells that the function is to average when it finds a match in the Range argument identified in step 5 of the tutorial.

This argument is optional and, if omitted, Excel averages the cells that are specified in the Range argument.

Since we want the average sales for the East sales region we use the data in the Total Sales column as the Average_range argument.

Tutorial Steps

  1. Click on the Average_range line in the dialog box.
  2. Highlight cells E3 to E9 on the spreadsheet. If the criteria specified in the previous step matches any data in the first range (C3 to C9), the function will average the data in the corresponding cells in this second range of cells.
  3. Click OK to close the dialog box and complete the AVERAGE IF function.
  4. A #DIV/0! error will appear in cell E12 – the cell where we entered the function because we have not yet added the data to the Criteria field (D12).

Adding the Search Criteria

Adding the Search Criteria
Adding the Search Criteria. © Ted French

The last step in the tutorial is to add the criteria we want the function to match.

In this case we want to find the average yearly sales for sales reps in the East region so we will add the term East to D12 – the cell identified in the function as containing the criteria argument.

Tutorial Steps

  1. In cell D12 type East and press the Enter key on the keyboard.
  2. The answer $59,641 should appear in cell E12. Since the criterion of equaling East is met in four cells (C3 to C6) the numbers in the corresponding cells in column E (E3 to E6) are averaged.
  3. When you click on cell E12, the complete function
    = AVERAGEIF (C3:C9, D12, E3:E9) appears in the formula bar above the worksheet.
  4. To find the sales average for other sales regions, type the name of the region, such as North in cell E12 and press the Enter key on the keyboard.
  5. The average for that sales region should appear in cell E12.