How to Use Excel AVERAGEIFS Function (8 Suitable Examples)

In this article, we will be showing how you can use the Excel AVERAGEIFS function to calculate some averages while maintaining one or more criteria in Excel. To do so, we will go through 8 easy examples.

AVERAGEIFS Function of Excel (Quick View):

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

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“ and 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.

Excel AVERAGEIFS Function: 8 Examples

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

2. Use of Single Criteria for Blank Cells

In this example, we will show you how to use the AVERAGEIFS function to find out the average number of goals in the drawn matches. Drawn matches are represented with blank cells inside the Result Column. Here, we have already marked the cells containing corresponding number of goals for the drawn matches and the results themselves with yellow color.

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.

3. Use of Single Criteria for Cells That are Not Blank

In this example, we will show you how to use the AVERAGEIFS function to find out the average number of goals in the Winning/Losing matches. Drawn matches are represented with blank cells inside the Result Column. Here, we have already marked the cells containing corresponding number of goals for the winning/losing matches and the results themselves with yellow color.

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.

4. 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 assisted 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.

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

Read More: How to Use Excel AVERAGEIFS Function with Multiple Criteria

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

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

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

Read More: How to Apply AVERAGEIFS Function Between Two Dates in Excel

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.

Read More: How to Fix AVERAGEIFS Value (#VALUE!) Error in Excel

Practice Section

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

Things to Remember

• Blank or empty cells are ignored by the AVERAGEIFS function. If a cell in the average_range is blank or contains text, it won’t be included in the average calculation, even if it meets the specified criteria.
• If no cells meet the specified criteria, the AVERAGEIFS function returns the #DIV/0! error. You can use error handling techniques, such as the IFERROR function, to display a custom message or handle the error gracefully.
• The ranges specified in criteria_range1, criteria_range2, etc., must be of the same size as the average_range. If the ranges are not of the same size, the function will return an error.

Frequently Asked Questions

• What is the difference between the Averageif() and Averageifs() functions?

AVERAGEIF() is used for single criterion averaging, while AVERAGEIFS() allows for averaging based on multiple criteria

• Is it possible to use logical operators, such as “AND” or “OR,” within the criteria of the AVERAGEIFS function in Excel to combine multiple conditions for averaging?

Yes, you can use logical operators within the criteria of the AVERAGEIFS function in Excel to combine multiple conditions for averaging. For example, you can use the “AND” operator to specify that all the conditions must be met, or the “OR” operator to specify that any of the conditions can be met.

Download Practice Workbook

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

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.

Excel AVERAGEIFS Function: Knowledge Hub

<< Go Back to Excel Functions | Learn Excel