AVERAGEIFS Function

What is the AVERAGEIFS Function?

The Averageifs Function returns the average of the numbers with the range of cells selected that meet the criteria. It is a Worksheet function in Microsoft Excel and falls under the Excel Statistical functions.

Using this function, the equation is calculated through a formula in a cell, and a numeric value is always returned. Additionally, it can be used on Excel for Office 365, Excel 2007, Excel 2011 for Mac, and any other later Excel versions.

The criteria can consist of several different operators, such as logical operators (<,>,=), dates, text, numbers, and wildcards(*,?).

People use several different functions in Excel to get the return they desire. For example, here are two functions that give similar returns to AVERAGEIFS():

1. Sumifs: Returns the number sum of numbers that meet criteria
2. AVERAGE: Returns the average of numbers from a range of selected cells

The difference between the Average() and Averageifs() functions is that the average function does not allow for any criteria.

AVERAGEIFS Formula

For the AVERAGEIFS Function to be used, it's necessary first to understand the purpose of the function. The general purpose is to find the average numbers that meet the criteria.

The syntax of the AVERAGEIFS Function is:

AVERAGEIFS(average_range,criteria_range1,range1,[criteria_range2,range2])

Let's separate this into individual arguments to understand it better:

• AVERAGEIFS() (Required): This is the function you will need to type into your cell on the sheet to begin the process of calculating your return.
• average_range (Required): One or more selected cells averaged and applied to the criteria.
• Criteria_range(1) (Required): The criteria to be applied to the range include expressions and logical operators.
• Range2 & criteria2 (Optional): Only necessary if you want to apply multiple criteria to different ranges. Each condition to be average requires it to be over a set of ranges and criteria, for example, Range1, criteria1, range2, criteria2… range30, criteria30… and so on.

Each required part of the argument must be entered for the function to compute correctly. For example, criteria and range are separated into two different arguments. The criteria must be enclosed with quotations (“”) for recognition.

Wildcard characters can be used as criteria. For this to work, a question mark would be entered to match a single character (?). An asterisk would be used to match sequences of character (*).

The averageifs function is similar to the average() and averageif() functions and measures its calculations based on the central tendency.

How to use the AVERAGEIFS Function in Excel?

The best way to understand how to use Excel functions is through examples. We can apply this to real-life scenarios and look at the syntax.

Example 1: For the first example, we’ll use a real-life example for knowledge of real-life applications. In this scenario, we’ll envision ourselves as teachers who want to find the average exam grades of our students according to criteria.

We have a small classroom of 5 students: Emily, Ryan, Taylor, Wyatt, and Maddy. Their scores of 95, 88, 52, 92, and 83 are listed in Figure 1. We want to find the average of all students who passed, with scores above 70 on Exam 1.

1. Our (average_range) in this scenario is the range of Exam 1 scores. 
   • 95
   • 88
   • 57
   • 92
   • 83
2. Our (criteria_range) in this scenario is also the range of Exam 1 scores.
   • 95
   • 88
   • 57
   • 92
   • 83
3. Our (criteria1) in this scenario is the condition we are testing our ranges on.
   • “>70”
4. We implemented each number in the correct arguments for our formula to find our outcome. 

Example 2

Let's use another example with the student's test scores from the data in Figure 2. This time let’s find the average of the students who passed the exam but did not score over 90. Therefore, we want to find the scores represented by 70 < x < 90 (x being the average).

We will use data from the same group of students, Emily, Ryan, Taylor, Wyatt, and Maddy, but this time look at their Exam 2 scores. The difference between Example 2 versus Example 1 is that we are testing Example two based on two different criteria seen in Figure 2.

1. Our (average_range) in this scenario is the range of Exam 2 scores. 
   • 90
   • 94
   • 61
   • 76
   • incomplete
2. Our (criteria_range) in this scenario is also the range of Exam 2 scores.
   • 90
   • 94
   • 61
   • 76
   • incomplete
3. Our (criteria1) in this scenario is the condition we are testing our ranges on.
   • “>70”
4. Our (criteria_range2) in this scenario is also the range of Exam 2 scores that will be tested with our second criteria. 
   • 90
   • 94
   • 61
   • 76
   • incomplete
5. Our (criteria2) in this scenario is the condition we are testing our criteria_range2 on.
   • “<90”

Example 3

Still using the scenario of students and test scores, for this example, let’s find the average scores from Exam 1 of students with an A Letter grade in the course. For this to compute correctly, we’ll only need to apply one text criterion, represented in Figure 3.

1. Our (average_range) in this scenario is the range of Exam 1 scores. 
   • 95
   • 88
   • 57
   • 92
   • 83
2. Our (criteria_range) in this scenario is also the range of Exam 1 scores.
   • 95
   • 88
   • 57
   • 92
   • 83
3. Our (criteria1) in this scenario is the condition we are testing our ranges on.
   • “A”
4. We implemented each number in the correct arguments for our formula to find our outcome. 

Example 4

Criteria can also be a value from another cell. For example, we could find the average range of numbers compared to a value in a cell. To calculate this, use the cell number paired with the desired logical operator as your criteria. This is represented by Figure 4 below.

Example 5

In this example, we’ll explore wildcards and how they can be applied to this function. As previously mentioned, question marks(?) match values to one character, and asterisks(*) match values to multiple characters. Tildes (~) are used to find an actual match to the wildcard itself.

For our scenario, we want to find The average of the values in B8:B12 when the values in D8:D12 include the text “blue.” The syntax for this formula would look like this:

=AVERAGEIFS(B8:B12,D8:D12,“*red*”)

AVERAGEIFS Function Common Errors

When your AverageIfs function isn’t working as expected, there can be a couple of different things that could be causing it. When using Excel, it's normal to get repeated errors.

Here are some of the most common errors and mistakes users typically make with this function:

1. #VALUE!

• This will occur if different-sized criteria and average ranges are inputted.
• This will occur if only one value instead of a range is entered into the average_range argument.

   

2. #NAME?

• This will occur if a non-numeric value is entered into the average_range argument. 
• This is because Excel only recognizes numbers for this argument and has no range to apply the criteria.

3. #DIV/0!

• This will occur if average_range and/or criteria_range do not meet the specified criteria.
• Excel has no possible value to return to the user since no value in the range meets the criteria.

If your function is still not working, here are other solutions to common mistakes:

• Make sure to double-check the syntax for incorrect ordering or missing values.
• Criteria and Range should always be the same size.
• If using logical operators, be aware that they are the right operator you desire.
• Users often misclick data when inputting formulas. 
• For this function, ensure you input the correct ranges for Average_range and Critera_range. Do not switch them.
• Users often mistake AverageIF for AverageIFS and will use them interchangeably. Unfortunately, not only do they have different syntax, but they are two different functions that will give you two different returns.

Researched & authored by Emily Rustom | Linkedin

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• AVERAGE Function
• AVERAGEIF Function
• BASE Function
• BINOM.DIST Function
• NORMDIST Function