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.
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:
| 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.
AVERAGEIF Function in Excel: 10 Productive Uses
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, 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:
=AVERAGEIF(F5:F19,C22)Or,
=AVERAGEIF(F5:F19,”>70”)➤ Press Enter and you’ll get the resultant value at once.
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.
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 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.
4. Application of AVERAGEIF Function Excluding Certain Text
Sometimes our dataset may contain some values related to some text that we may have to exclude. For example, we have to exclude the scores of those citizens who reside in Texas.
📌 Steps:
➤ The related formula in the output cell I7 will be:
=AVERAGEIF(E5:E19,"<>"&I5,F5:F19)➤ After pressing Enter, you’ll get the average of the scores without values related to Texas.
Again if we need to exclude the scores of the citizens who reside in a state which as the text ‘lab’ inside it, the required formula in cell I7 should be:
=AVERAGEIF(E5:E19,"<>"&"*"&I5, F5:F19)After pressing Enter, you’ll find the expected result as in the picture below.
5. Use 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.
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 C23 or cell C24 should be:
=AVERAGEIF(B5:B19,"=",F5:F19)Or,
=AVERAGEIF(B5:B19,"",F5:F19)(To average cells in a given column, if a cell in another column in the same row is blank, use “=” for criteria. This will include empty cells that contain absolutely nothing – no space, no zero-length string, no non-printing characters, etc.)
After pressing Enter, you’ll find the expected result as in the picture below.
6. Application of AVERAGEIF Function Excluding Zero Values
Often, our dataset may contain some zero values which we may need to exclude. For example, we have to exclude the zero scores while calculating the average from column F (Both positive and negative values are acceptable).
📌 Steps:
➤ The related formula in the output cell C22 will be:
=AVERAGEIF(F5:F19, "<>0")➤ After pressing Enter, you’ll get the average of the scores without considering the zero values.
7. AVERAGEIF Function with Comparison Operator
From the table below, 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.
8. 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.
9. 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.
10. 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 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’ll see a drop-down at the top-left corner which is known as the 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:
=AVERAGEIF(BirthDate,">12/31/1995",Score)➤ 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.
Frequently Asked Questions
- What is the difference between the AVERAGEIF() and AVERAGEIFS() functions?
The main functional distinction between the two is that although AVERAGEIFS can handle one or more criteria, AVERAGEIF can only manage one condition.
- Can you use Averageif with multiple ranges?
The AVERAGEIF() function in Excel cannot be used to determine an average value utilizing multiple ranges.
Download Practice Workbook
You can download the Excel workbook that we’ve used to prepare this article.
Concluding Words
I hope all of the methods mentioned above to use the Excel 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.
