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

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

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

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.

- Click on
**cell E12**to make it the active cell — this is where we will enter the**AVERAGEIF**function. - Click on the
**Formulas tab**of the**ribbon**. - Choose
**More Functions > Statistical**from the ribbon to open the function drop-down. - 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

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.

- In the
**Formula Builder**, click on the**Range**line. - 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

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.

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

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.

- Click on the
**Average_range**line. - 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. - 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

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.

- In
**cell****D12**type**East**and press the**Enter**key on the keyboard. - 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**.