How to Use Excel AVERAGEIFS Function (6 Suitable Examples)

https://www.exceldemy.com/how-to-find-mean-median-and-mode-on-excel/In this article, we will be showing how you can use the AVERAGEIFS function of Excel to calculate some averages while maintaining one or more criteria in Excel. To do so, we will go through 6 easy examples.

AVERAGEIFS Function of Excel (Quick View):

In the following picture, you can see the overview of the AVERAGEIFS function.

Download Practice Workbook

You can download the following Excel file and practice while reading this article.

Excel AVERAGEIFS Function: Syntax and Argument

Summary

• The AVERAGEIFS function returns the average of the cells of an array that satisfy one or more given criteria. Here, the criteria can be of the same array or a different array.
• Available from Excel 2007.

Syntax

The Syntax of the AVERAGEIFS function is:

Argument

Notes:

• Only 1 criterion along with1 range of cells, where the criteria will be applied (criteria_range), is essential. However, you can use multiple criteria if you need.
• Both the criteria and the criteria_range must come together like a pair. That means if you input criteria_range 2, you must input criteria2.
• Both the average_range and all criteria_ranges must be equal. Otherwise, Excel will raise #VALUE!
• While calculating the average of values, Excel will count only those cell values that satisfy all the criteria.

Return Value

Returns the average of the cells of an array that satisfy one or more given criteria.

Special Notes

• If the criterion is equal to the cell value or cell reference, then in the place of criteria, you can simply put the value or the cell reference.

Like this:

OR

OR

• When the criterion denotes greater than or less than some value, enclose the criteria within an apostrophe (“”)

Like this:

• When the criterion denotes greater than or less than some cell reference, enclose only the greater than or the less than symbol within an apostrophe (“”) and then join the cell reference by an ampersand (&)

Like this:

• You can have partial matches too within the AVERAGEIFS

For matching any one character in a specific place, use “?”.

For example, “?end” will match “bend”, “send” but not “spend” or “end”.

And for matching any number of characters including zero, use “*”.

For example, “*end” will match “end”, “bend”, “send”, “spend” all.

So the AVERAGEIFS formula will look like:

OR

• If any cell within the average_range contains a text value other than a number, AVERAGEIFS will not count that even if it satisfies all the criteria. Because it is only possible to calculate the average of some numbers, not any text.

6 Examples of Using Excel AVERAGEIFS Function

The following data set has the Opponent, Goals, Assists, Results, and Venue columns. Furthermore, using this dataset, we will demonstrate 6 examples to show the usages of the AVERAGEIFS function. Here, we used Excel 365. You can use any available Excel version.

1. Using Single Criteria for Equal To Value in AVERAGEIFS Function

In this example, we will show you how you can use the AVERAGEIFS function using single criteria equal to a value. Afterward, using the AVERAGEIFS function, we will find out the average Goals based on the criteria when the Result is Won.

Here, we have already marked the Goals and criteria Won with yellow color, and we will find out the average of the goals that have a yellow color.

Steps:

• First of all, we will type the following formula in cell H6.

• After that, press ENTER.

As a result, you can see the result in cell H6.

Read More: How to Use AVERAGE Function in Excel (5 Examples)

2. Use of Single Criteria for Greater Than Value

In this example, we will show you how to use the AVERAGEIFS function to find out the Goals that have Assists numbers greater than or equal to 1. Here, we have already marked the number of Assists that have a value greater than or equal to 1 and the number of Goals based on the Assists with yellow color. Next, we will calculate the average of these goals based on the criteria.

Steps:

• To begin with, we will type the following formula in cell H6.

• Afterward, press ENTER.

Therefore, you can see the result in cell H6.

Similar Readings

• How to Use LINEST Function in Excel (4 Suitable Examples)
• Use RANK Function in Excel (With 5 Examples)
• How to Use VAR Function in Excel (4 Examples)
• Use PROB Function in Excel (3 Examples)
• How to Use Excel STDEV Function (3 Easy Examples)

3. Applying Multiple Criteria in AVERAGEIFS Function

In this example, we will demonstrate the use of the AVERAGEIFS function based on multiple criteria.

Here, we will find out the average of goals when Goals number is at least 1, and when the Venue is Home. We have marked both criteria with a Yellow color.

Steps:

• First, we will type the following formula in cell H6.

• At this point, press ENTER.

Therefore, you can see the result in H6.

Again, we will find out the average of goals when the Goals number is greater than or equal to 1, and when the Assists number is also greater than or equal to 1. We have marked both criteria with Yellow color.

• After that, we will type the following formula in cell H6.

• At this point, press ENTER.

Therefore, you can see the result in H6.

4. Counting Average with Partial Match (Wildcard Character)

In this example, we will show you how to calculate the average using the AVERAGEIFS function when the criteria match partially. We will use a Wildcard character for this purpose. You see, there are two Korea on the Opponent list, North Korea and South Korea. Next, we will find out the average of the goals of the Opponent that has Korea in their name. Here, we marked the Opponent and the corresponding Goal number with Yellow color.

Steps:

• First, we will type the following formula in cell H6.

• Furthermore, press ENTER.

Hence, you can see the result in cell H6.

If you want to know more about the wildcard characters, you can visit this link.

Read More: The Different Ways of Counting in Excel

5. Using Cell References in AVERAGEIFS Function

In this example, we will use the cell reference instead of text in the AVERAGEIFS function. We will use a single criterion for this purpose.

Here, we will find out the average Goals based on the criteria when the Result is Won. In the formula, instead of typing Won, we will simply select cell E6.

We have already marked the Goals and criteria Won with yellow color, and we will find out the average of the goals that have a yellow color.

Steps:

• First of all, we will type the following formula in cell H6.

• After that, press ENTER.

As a result, you can see the result in cell H6.

6. Applying Date Range in AVERAGEIFS Function

Here, we will show you the use of the AVERAGEIFS function when there is a Date range, and we want to find out the average based on Dates. For this purpose, we modified the previous dataset and added a Date column to it.

After that, we want to find the average of the goals that include the date from 20-Mar-22 to 08-Aug-22. Here, we marked these dates with Yellow color.

Steps:

• In the beginning, we will type the following formula in cell H6.

• At this point, press ENTER.

Therefore, you can see the result in H6.

Common Errors with Excel AVERAGEIFS Function

In the following table, we have shown the common errors of the AVERAGEIFS function, and the reasons for occurring such errors.

Practice Section

You can download the above excel file and, therefore, practice the explained examples.

Conclusion

Here, we tried to show you 6 examples of the usage of the AVERAGEIFS function in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website Exceldemy to explore more.

Related Articles

• How to calculate Average, Median, & Mode in Excel
• Use SMALL Function in Excel (4 Common Examples)
• How to Use MIN Function in Excel (5 Relevant Examples)
• How to use MAX function in Excel (6 Examples)
• Use LARGE Function in Excel (6 Easy Examples)
• How to Use COUNTIF Function in Excel (10 Suitable Applications)