How to Use AVERAGEIF Function in Excel (8 Suitable Applications)

In Microsoft Excel, the AVERAGEIF function is widely used to determine the average or arithmetic mean from a range of data under a specified condition. In this article, you’ll get to learn how you can use this AVERAGEIF function efficiently in Excel.

The above screenshot is an overview of the article, representing an application of the AVERAGEIF function in Excel. You’ll learn more about the dataset as well as the methods to use the AVERAGEIF function properly in the following sections of this article.

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

Introduction to the AVERAGEIF Function

• Function Objective:

Finds average for the cells specified by a given condition or criteria.

• Syntax:

=AVERAGEIF(range, criteria, [average_range])

• Arguments Explanation:

• Return Parameter:

The arithmetic mean in a numerical value.

8 Productive Uses of AVERAGEIF Function in Excel

1. Using AVERAGEIF Function with Two Arguments to Find out the Average in Excel

Before getting down to the uses of the AVERAGEIF function in Excel, let’s get introduced to our dataset first. The picture below represents a contest among some people from different states. By using the AVERAGEIF function, we’ll find out the average of the scores under different criteria.

As the 3rd argument in the AVERAGEIF function is optional, so at first, let’s see how the function works with the first two required arguments only. We’ll directly input the primary range of cells to the range of criteria and then define the criteria for that range.

Based on our dataset, we’ll determine the average of the scores that are more than 70.

📌 Steps:

➤ Select the output Cell C24 and type:

Or,

➤ Press Enter and you’ll get the resultant value at once.

Read More: How to calculate Average, Median, & Mode in Excel

2. Using AVERAGEIF Function with Three Arguments to Find out the Average in Excel

Now we’ll find out how the function works with all three arguments. From the table below, we’ll determine the average score of the male participants only. The 3rd argument here will be the range of cells containing scores of all participants and the first two arguments will incorporate the Gender column and the defined criteria.

📌 Steps:

➤ In the output Cell C24, we have to type:

➤ After pressing Enter, the average score of the male participants will be shown right away.

Read More: How to Find Average If Values Lie Between Two Numbers in Excel

3. Use of AVERAGEIF Function with Wildcard Characters in Excel

By using a wildcard character, such as Asterisk (*), we can define a text criterion with a partial match. Assuming that we want to know the average of scores of a particular state containing the text ‘lab’ inside its name.

📌 Steps:

➤ In Cell C24, the related formula will be:

Or,

➤ Now press Enter and the formula will return the average score of all the participants from Alabama state as the text ‘lab’ is present in the name of this state.

Read More: How to Find Average If Cell Contains Text in Excel (4 Useful Ways)

4. Application of AVERAGEIF Function with or without Blank Cells Criteria

Sometimes our dataset may contain blank cells in a column and we may have to exclude the rows containing those blanks while evaluating the average of the numeric values from a dataset. For example, we want to know the average of the scores of those participants whose names are visible in Column B.

📌 Steps:

➤ The related formula in the output Cell C24 will be:

➤ After pressing Enter, you’ll get the average of the scores with the defined criteria.

And if we need to know the average score of the participants whose names are not present in Column B, then the required formula in Cell C24 should be:

After pressing Enter, you’ll find the expected result as in the picture below.

Similar Readings

• How to Use MODE Function in Excel (4 Examples)
• Use VAR Function in Excel (4 Examples)
• How to Use PROB Function in Excel (3 Examples)
• Use Excel STDEV Function (3 Easy Examples)

5. AVERAGEIF Function with Comparison Operator

From the table below, now we’ll determine the average score of the participants not more than 25 years old. So, we’ll have to use a specific comparison operator- ‘Less than or equal to (<=)’ before the numeric value 25 as the defined criteria for the Age column.

📌 Steps:

➤ Select the output Cell C24 and type:

Or,

➤ Press Enter and the return value will be displayed immediately.

Read More: Excel AVERAGEIF with ‘Greater Than’ and ‘Less Than’ Criteria

6. AVERAGEIF with OR Logic in Excel

When we have to evaluate the average of a range of numeric values based on the multiple criteria from a similar column, we’ll need to find out the averages for those multiple criteria separately. After that, we have to insert the AVERAGE function to enclose those two parameters. For example, by following this procedure, we can determine the average score of the participants from two specific states- Alabama and Texas.

📌 Steps:

➤ Select Cell C24 and input the following formula:

➤ Press Enter and you’ll get the resultant value at once.

Read More: How to Calculate Average If Number Matches Criteria in Excel

7. Use of AVERAGEIF Function with Dates Criteria in Excel

In this section of the article, we’ll evaluate the average score of those participants who were born after 1995.

📌 Steps:

➤ Select the output Cell C24 and the related formula with the date input will be:

➤ After pressing Enter, you’ll get the average score of the participants with the specified date condition.

8. AVERAGEIF Function with Named Range in Excel

By using a dynamic named range for the specific cells and inputting that named range inside the AVERAGEIF function, we can also evaluate the average with multiple conditions. Here, we’ll find out a similar result obtained in the previous section but this time we’ll use the named range inside the formula. So, we have to define the names for the range of cells from the specific columns first.

In the picture below, you’re seeing a drop-down at the top-left corner which is known as Name Box. For example, we want to give a name to the range of cells containing all birth dates from Column D. So, we have to select the range of cells D5:D19 first and then edit in the Name Box with a particular name. You have to keep in mind that the name must not include a space while defining it in the Name Box. Thus you can select all columns and give names to all of the data based on the data types or headers.

Now we’ll apply the AVERAGEIF function and input the named ranges to evaluate the average score of the participants who were born after 1995.

📌 Steps:

➤ Select the output Cell C24 and type:

➤ Press Enter and you’ll find the resultant value right away.

💡 Things to Keep in Mind

🔺 AVERAGEIF function is unable to include multiple conditions and in that case, you have to use the AVERAGEIFS function.

🔺 Unless the AVERAGEIF function meets the criteria in the range of cells, the function will return a #DIV/0 error.

Concluding Words

I hope all of the methods mentioned above to use the AVERAGEIF function will now prompt you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

You May Also Like to Explore

• How to Use Excel FREQUENCY Function (6 Examples)
• Use TREND Function in Excel (3 Examples)
• How to Use Excel NORMDIST Function (2 Applications)
• Excel AVERAGEIF Function for Values Greater Than 0
• How to Use COUNTIFS Function in Excel (4 Examples)