title: “How To Use Averageif Function In Excel” ShowToc: true date: “2022-12-07” author: “Mark Jones”
What is AVERAGEIF?
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. Often, AVERAGEIF uses rows of data called records, in which all of the data in each row is related.
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:
The function’s arguments tell it what condition to test for and the range of data to average when it meets that condition.
Range (required) is the group of cells the function will search for the specified criteria. Criteria (required) is the value compared against the data in the Range. You can enter actual data or the cell reference for this argument. Average_range (optional): The function averages the data in this range of cells when it finds matches between the Range and Criteria arguments. If you omit the Average_range argument, the function instead averages the data matched in the Range argument.
In this example, the AVERAGEIF function is looking for the average yearly sales for the East sales region. The formula will include:
A Range of cells C3 to C9, which contains the region names. The Criteria is cell D12 (East). An Average_range of cells E3 to E9, which contains the average sales by each employee.
So if data in the range C3:C12 equals East, then the total sales for that record are averaged by the function.
Entering the AVERAGEIF Function
Although it is possible to type the AVERAGEIF function into a cell, many people find it easier to use the Function Dialog Box to add the function to a worksheet.
Begin by entering the sample data provided into cells C1 to E11 of an empty Excel worksheet as seen in the image above.
In Cell D12, under Sales Region, type East.
Click on cell E12 to make it the active cell, which is where the AVERAGEIF function will go. 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 to open the Function Dialog Box. The data that goes into the three blank rows in the Function Dialog Box makes up the arguments of the AVERAGEIF function. Click 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. 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. Although you can input actual data – such as the word East – for this argument, it is usually more convenient to add the data to a cell in the worksheet and then input that cell reference into the dialog box. 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. The answer $59,641 should appear in cell E12.
When you click on cell E12, the complete function appears in the formula bar above the worksheet.
Using a cell reference for the Criteria Argument makes it easy to find to change the criteria as needed. In this example, you can change the content of cell D12 from East to North or West. The function will automatically update and display the new result.
