How to Use AVERAGEIFS Function in Excel (6 Suitable Examples)

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.

Overview for Excel 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:

=AVERAGEIFS(average_range,criteria_range1,criteria1,...)

Argument

Argument Required or Optional Value
average_range Required The array of cells whose average is to be determined.
criteria_range1 Required The array of cells that needs to satisfy the first criteria.
criteria1 Required The first criteria.
criteria_range2 Optional The array of cells that needs to satisfy the second criteria.
criteria2 Optional The second criteria.

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:

=AVERAGEIFS(C5:C9,C5:C9,1)

OR

=AVERAGEIFS(C5:C9,C5:C9,"Won")

OR

=AVERAGEIFS(C5:C9,C5:C9,A2)
  • When the criterion denotes greater than or less than some value, enclose the criteria within an apostrophe (“”)

Like this:

=AVERAGEIFS(C5:C9,C5:C9,">1")
  • 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:

=AVERAGEIFS(C5:C9,C5:C9,">"&A2)
  • 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:

=AVERAGEIFS(C5:C9,C5:C9,"?end")

OR

=AVERAGEIFS(C5:C9,C5:C9,"*end")
  • 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.

Dataset for Excel AVERAGEIFS Function


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.
=AVERAGEIFS(C6:C23,E6:E23,"Won")

Excel AVERAGEIFS Function with Single Criteria

Formula Breakdown

  • AVERAGEIFS(C6:C23,E6:E23,”Won”) → Calculates the average of only those cells in the array C6 to C23 whose corresponding cells in the array E6 to E23 contain “Won”.
    • Output: 2.09
  • 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.
=AVERAGEIFS(C6:C23,D6:D23,">=1")

Formula Breakdown

  • AVERAGEIFS(C6:C23,D6:D23,”>=1″) → Calculates the average of only those cells in the array C6 to C23 whose corresponding cells in the array D6 to D23 contain anything greater than or equal to 1.
    • Output: 1.80
  • Afterward, press ENTER.

Therefore, you can see the result in cell H6.

Excel AVERAGEIFS Function with criteria Greater Than a Value

Similar Readings


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.
=AVERAGEIFS(C6:C23,C6:C23,">=1",F6:F23,"Home")

Formula Breakdown

  • AVERAGEIFS(C6:C23,C6:C23,”>=1″,F6:F23,”Home”) → Calculates the average of only those cells in the array C6 to C23 that contain anything greater than or equal to 1 and whose corresponding cells in the array F6 to F23 contain “Home”.
    • Output: 2.33
  • At this point, press ENTER.

Therefore, you can see the result in H6.

Excel AVERAGEIFS Function with Multiple Criteria

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.
=AVERAGEIFS(C6:C23,C6:C23,">=1",D6:D23,">=1")

Formula Breakdown

  • AVERAGEIFS(C6:C23,C6:C23,”>=1″,D6:D23,”>=1″) → Calculates the average of only those cells in the array C6 to C23 that contain anything greater than or equal to 1 and whose corresponding cells in the array D6 to D23 contain anything greater than or equal to 1.
    • Output: 2.33
  • 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.
=AVERAGEIFS(C6:C23,B6:B23,"*Korea")

Using Excel AVERAGEIFS Function for Partial Match

Formula Breakdown

  • AVERAGEIFS(C6:C23,B6:B23,”*Korea”) → Calculates the average of only those cells in the array C6 to C23 whose corresponding cells in the array B6 to B23 contain anything having “Korea” at the end.
    • Output: 2
  • 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.

=AVERAGEIFS(C6:C23,E6:E23,E6)

Formula Breakdown

  • AVERAGEIFS(C6:C23,E6:E23,E6) → Calculates the average of only those cells in the array C6 to C23 whose corresponding cells in the array E6 to E23 contain the cell content of cell E6 that is “Won”.
    • Output: 2.09
  • 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.
=AVERAGEIFS(C6:C23,F6:F23,"<=8-Aug-22",F6:F23,">=20-Mar-22")

Formula Breakdown

  • AVERAGEIFS(C6:C23,F6:F23,”<=8-Aug-22″,F6:F23,”>=20-Mar-22″) → Calculates the average of only those cells in the array C6 to C23 whose corresponding cells in the array F6 to F23 contain dates greater than or equal to 20-Mar-22 and less than or equal to 8-Aug-22 .
    • Output: 1.727272727
  • 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.

Error When They Show
#DIV/0! Shows when no value in the average_match matches all criteria.
#VALUE! This shows when the lengths of all the arrays are not the same.

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

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo