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.

averageif function overview 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

averageif function syntax

  • Function Objective:

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

  • Syntax:

=AVERAGEIF(range, criteria, [average_range])

  • Arguments Explanation:
Argument Required/Optional Explanation
range Required Range of cells that contains the criteria or condition.
criteria Required Condition for the criteria range.
[average_range] Optional Range of cells that is to be evaluated for arithmetic mean.
  • 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.

averageif function two arguments in excel

📌 Steps:

➤ Select the output Cell C24 and type:

=AVERAGEIF(F5:F19,C22)

Or,

=AVERAGEIF(F5:F19,”>70”)

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

averageif function two arguments in excel

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:

=AVERAGEIF(C5:C19,C22,F5:F19)

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

averageif function three arguments in excel

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:

=AVERAGEIF(E5:E19,C22,F5:F19)

Or,

=AVERAGEIF(E5:E19,”*lab*”,F5:F19)

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

averageif function with wildcard character in excel

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:

=AVERAGEIF(B5:B19,"<>",F5:F19)

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

averageif function with or without blank empty cells in excel

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:

=AVERAGEIF(B5:B19,"",F5:F19)

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

averageif function with or without blank empty cells in excel


Similar Readings


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:

=AVERAGEIF(D5:D19,C22,F5:F19)

Or,

=AVERAGEIF(D5:D19,”<=25”,F5:F19)

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

averageif function with comparison operator in excel

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:

=AVERAGE(AVERAGEIF(E5:E19,D21,F5:F19), AVERAGEIF(E5:E19,D22,F5:F19))

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

averageif function with multiple or criteria in excel

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:

=AVERAGEIF(D5:D19,">12/31/1995",F5:F19)

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

averageif function with dates criteria in excel


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.

averageif function with named range in excel

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:

=AVERAGEIF(BirthDate,">12/31/1995",Score)

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

averageif function with named range in excel


💡 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

Nehad Ulfat

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo