How to Use AVERAGEIFS Function in Excel (4 Examples)

Quick View of Excel AVERAGEIFS Function

Today I will be showing how you can use the AVERAGEIFS function of Excel to calculate some averages while maintaining one or more criteria in Excel.


AVERAGEIFS Function of Excel (Quick View)

Quick View of Excel AVERAGEIFS Function


Download Practice Workbook


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. The criteria can be of the same array or a different array.
  • Available from Excel 2007.

Syntax

Syntax of the AVERAGEIFS Function

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 one criterion and one range of cells where the criteria will be applied (criteria_range)  is compulsory. But you can use as many criteria as you wish.
  • The criteria and the criteria_range must come in pairs. That means if you input criteria_range 3, you must input criteria3.
  • The lengths of the average_range and all criteria_ranges must be equal. Otherwise, Excel will raise #VALUE! Error.
  • While calculating the average, Excel will count only those cells 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

  • When the criterion denotes equal to some value or cell reference, just put the value or the cell reference in place of the criteria.

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 (&) symbol.

Like this:

=AVERAGEIFS(C5:C9,C5:C9,">"&A2)
  • You can have partial matches too within the AVERAGEIFS function.

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.

Excel AVERAGEIFS Function: 4 Examples


1. Counting Average with a Single Criteria Equal to a Value

Look at the data set below. We have the records of the last two months of a soccer player named Alfred Moyes.

Data Set in Excel

Let’s try to find out his average of goals in the winning matches.

The formula will be:

=AVERAGEIFS(C5:C22,E5:E22,"Won")

AVERAGEIFS with Single Criteria Equal to a Value

We see, his average number of goals in the winning matches is 2.09. That is quite good, isn’t it?

Formula Output Explanation
=AVERAGEIFS(C5:C22,E5:E22,”Won”) 2.09 Calculates the average of only those cells in the array C5 to C22 whose corresponding cells in the array E5 to E22 contain “Won”.

Now if you understand this, can you tell me the formula to determine his goals average in away matches?

Yes. You are right. The formula will be:

=AVERAGEIFS(C5:C22,F5:F22,"Away")

2. Counting Average with a Single Criteria Greater than a Value

His statistics suggest that he did play well, but not played well in all the matches.

Now let’s try to find out his average number of goals in the matches when he had at least one assist.

The formula will be:

=AVERAGEIFS(C5:C22,D5:D22,">=1")

AVERAGEIFS with a Criteria Equal to a Value

You see, in the matches with assists, his average is 1.80. That is quite good also.

Formula Output Explanation
=AVERAGEIFS(C5:C22,D5:D22,”>=1″) 1.80 Calculates the average of only those cells in the array C5 to C22 whose corresponding cells in the array D5 to D22 contain anything greater than or equal to 1.

Now can you tell me the formula to determine his goals average in the matches when he scored at least one goal?

Yes. You are right. The formula will be:

=AVERAGEIFS(C5:C22,C5:C22,">=1")

3. Counting Average with Multiple Criteria

This time we will find out his goals average in the home matches, where he scored at least one goal.

The formula will be:

=AVERAGEIFS(C5:C22,C5:C22,">=1",F5:F22,"Home")

AVERAGEIFS with Multiple Criteria

See, this time his average is 2.33.

Formula Output Explanation
=AVERAGEIFS(C5:C22,C5:C22,”>=1″,F5:F22,”Home”) 2.33 Calculates the average of only those cells in the array C5 to C22 that contain anything greater than or equal to 1 and whose corresponding cells in the array F5 to F22 contain “Home”.

Now, if you understand this, can you tell me the formula to determine his goals average in the matches when he scored both goals and assists?

Quite easy. The formula will be:

=AVERAGEIFS(C5:C22,C5:C22,">=1",D5:D22,">=1")

AVERAGEIFS Function with Multiple Criteria

This time his average is, even more, 2.57 goals per match.


4. Counting Average with Partial Match

Finally, we will count the average with a partial match.

You see, there are two Koreas on the opponent list. North and South Korea.

Let’s try to find out his average number of goals against both the Koreas.

The formula will be:

=AVERAGEIFS(C5:C22,B5:B22,"*Korea")

AVERAGEIFS with Partial Match

See, his average against Korea is 2.00

Formula Output Explanation
=AVERAGEIFS(C5:C22,B5:B22,”*Korea”) 2.00 Calculates the average of only those cells in the array C5 to C22 whose corresponding cells in the array B5 to B22 contain anything having “Korea” at the end.

Note: Here we have used “*Korea” because we wanted to match any number of characters before the word “Korea”.

You can accomplish it using “???th Korea” too.

Remember, “?” matches any one character only.

AVERAGEIFS Function with Multiple Criteria

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


Common Errors with Excel AVERAGEIFS Function

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.

Conclusion

Using these methods you can use the AVERAGEIFS function of Excel to calculate the average of some values that maintain one or more criteria. Do you have any questions? Feel free to ask us.


Further Readings

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