Excel AVERAGEIF: Find the Average for Specific Criteria

Cropped Image Of Business People Discussing Over Graphs At Table In Office
Audtakorn Sutarmjam / EyeEm / Getty Images

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, while 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.

The information in this tutorial applies to Microsoft Excel 2007, 2010, 2016, and the latest edition of Office 365.

Entering the Tutorial Data

Screenshot of Excel showing the tutorial data

Following the steps in this tutorial walks you through creating and using the AVERAGEIF function to calculate average yearly sales in a sample data set. Through this process, you will learn how the function's criteria are set and how to average specified data.

Begin by entering the sample data provided into cells C1 to E11 of an empty Excel worksheet as seen in the image above.

The tutorial instructions do not include formatting steps for the worksheet. Your worksheet will look different than the example shown, but the AVERAGE IF function will give you the same results.

AVERAGEIF Function Syntax

Screenshot of Excel showing the complete AVERAGEIF syntax

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 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: The value 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

Screenshot of Excel showing the selection of the AVERAGEIF function

Although it is possible to type the AVERAGEIF function into a cell, many people find it easier to use the Formula Builder to add the function to a worksheet.

  1. Click on cell E12 to make it the active cell — this is where we will enter the AVERAGEIF function.
  2. Click on the Formulas tab of the ribbon.
  3. Choose More Functions > Statistical from the ribbon to open the function drop-down.
  4. Click on AVERAGEIF in the list.

The data that we enter into the three blank rows in the Formula Builder will form the arguments of the AVERAGEIF 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

Screenshot of Excel showing the Range argument

In this tutorial, we are looking to find out the average yearly sales for the East sales region. The Range argument tells the AVERAGEIF function which group of cells to search when trying to find the specified criteria.

  1. In the Formula Builder, 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

Screenshot of Excel showing the Criteria argument

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.

  1. Click on the Criteria line.
  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 criterion.

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 AVERAGEIF 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

Screenshot of Excel showing the Average_range argument

The Average_range argument is the group of cells that the function is to average when it finds a match in the Range argument; 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.

  1. Click on the Average_range line.
  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 Done to complete the AVERAGEIF function.

A #DIV/0! error will appear in cell — the cell where we entered the function because we have not yet added the data to the Criteria field (D12).

Adding the Search Criteria

Screenshot of Excel showing the entering of criteria into cell E12

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 cell D12 – the cell identified in the function as containing the criteria argument.

  1. In cell D12 type East and press the Enter key on the keyboard.
  2. The answer $59,641 should appear in cell E12 because the criterion of equaling East is met in four cells (C3 to C6) and the numbers in the corresponding cells in column E (E3 to E6) are averaged.
= AVERAGEIF (C3:C9, D12, E3:E9) 

When you click on cell E12, the complete function appears in the formula bar above the worksheet.

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. The average for that sales region should then appear in cell E12.