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