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

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