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.

**Table of Contents**hide

**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:**

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.

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

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

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.

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

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

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

**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:

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

**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 SUMPRODUCT Function in Excel (4 Examples)**

**How to Use RANK Function in Excel (With 5 Examples)**

**How to Use SUMIF Function in Excel (With 5 Easy Examples)**