How to Use The AVERAGEIFS Function

How to Use The AVERAGEIFS Function feature image

The need to find the average of a range of numbers is fairly common. For instance, imagine you want to know the average grade in a selection of exam results, or the average order value for a customer. Thankfully, Excel has some built-in functions that can provide this information quickly. The AVERAGEIFS function is one of them, and it is the focus of this blog post. The AVERAGEIFS function allows you to select numerical values from a range of cells based on multiple criteria that you specify, in fact up to 127 criteria.

In this blog post, you will learn how to use the AVERAGEIFS function with multiple criteria, both text and numerical examples; how to use named ranges with the AVERAGEIFS function, and the reasons for and types of Excel error messages the AVERAGEIFS function might return.

Quick Example

Okay, a quick example. We have a list of customer order values for customer’s A and B as shown below.

AVERAGEIFS Example Data

Suppose we want the average order value for customer A. To do this we use the following formula:

=AVERAGEIFS(B2:B11,C2:C11,”=Customer A”)

We insert the formula into cell E2:

AVERAGEIFS Example Formula

The answer is $446.80.

Syntax

The AVERAGEIFS function averages cells in a range that match certain criteria. The AVERAGEIFS function can include multiple criteria. The criteria and the cell ranges against which the criteria are evaluated work in pairs. The AVERAGEIFS function only requires one pair of criteria and a criteria range. However, you can optionally have up to 127 pairs of criteria and criteria ranges.

The syntax of the AVERAGEIFS function is AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …).

ParameterDescription 
average_rangethe average_range parameter is the range of cells containing the numbers to average. This parameter is required.
criteria_range1the criteria_range1 parameter is the range of values that is evaluated against the criteria1 parameter. This parameter is required.
criteria1the criteria1 parameter is the logical statement that is used to assess the values in the criteria_range1 parameter.
The criteria1 parameter can be a number, an expression, a cell reference, or a text string. With the exception of a cell reference or named range, the criteria parameter value must be enclosed in quotation marks ().
The criteria1 parameter is not case-sensitive. Therefore, the AVERAGEIFS function will match DOG, Dog, and dog exactly the same way. This parameter is required.
[criteria_range2, criteria2 …]the [ criteria_range2, criteria2 …] parameters are optional pairs of parameters. You can specify up to 127 pairs of parameters.

There are three common types of average calculations, mean, median, and mode. The type of average that the AVERAGEIFS function calculates is the mean, sometimes called arithmetic mean. Simply put, the average is calculated by adding a group of numbers together, and then dividing the total by how many numbers there are in the group. For information on the different types of average calculation, click here.

Examples

All of the following examples all use the same dataset that contains student information and exam results. The exam result is stored in column B, the student’s age in column C, the student’s gender in column D, and the location of study in column E as shown in the image below.

AVERAGEIFS Example Student Data

Using Numbers as the Criteria

For our first example, imagine you want to get the average mark for students aged between and including 21 and 30. In order to do this, we use the comparison operators in the table below.

OperatorExampleDescription
>>10greater than a specified value, in this example greater than 10
<<10less than a specified value , in this example less than 10
=10equal to a specified value , in this example equal to 10
<><>10not equal to a specified value, in this example not equal to 10
>=>=10greater than or equal to a specified value, in this example greater than or equal to 10
<=<=10less than or equal to a specified value, in this example less than or equal 10

We need to use two comparison operators, the greater than or equal to operator (>=), and the less than or equal to operator (<=) in the following formula:

=AVERAGEIFS(B3:B51,C3:C51,”>=21″,C3:C51,”<=30″)

We insert the formula in cell H3.

AVERAGEIFS Students Aged 21-30

The average mark for students aged between and including 21 and 30 years of age is 70.125 as you can see from the image above. Let’s take a closer look at the formula:

  • average_range – the average_range parameter is the range of cells B3:B51 containing the students’ exam results. Remember the AVERAGEIFS formula only averages the rows that match ALL the criteria that are tested in the criteria_range and criteria parameter pairs.
  • criteria_range1 – the criteria_range1 parameter is the range of cells C3:C51 that contains the student ages.
  • criteria1 – the criteria1 parameter uses the greater than or equal to (>=) comparison operator. It tests to see if the student is greater than or equal to 21 years old using the expression “>=21.”
  • criteria_range2 – the criteria_range2 parameter is again the range of cells C3:C51 that contains the student ages.
  • criteria2 – the criteria2 parameter uses the less than or equal to (<=) comparison operator to test if the student is less than or equal to 30 years old using the expression “<=30.

Using Text as the Criteria

Now, let’s imagine that we want to get the average exam mark for males. Again, we use a comparison operator. This time, we are going to use the equals to operator (=) as shown in the formula below:

=AVERAGEIFS(B3:B51,D3:D51,”=Male”)

As in the previous example, we insert the formula into cell H3.

AVERAGESIFS Example Male Student

The average mark for male students is 69, as you can see from the image above. Let’s take a closer look at what the formula is doing:

  • average_range – the average_range parameter is the range of cells B3:B51 containing the students’ exam results.
  • criteria_range1 –  the criteria_range1 parameter is the range of cells D3:D51 that contains the students’ gender.
  • criteria1 – the criteria1 parameter uses the equal to (=) comparison operator to test whether or not the student is male, using the expression “=Male”.

Wildcard Characters

You can use wildcard characters in the criteria parameters. Going back to our student example, you can get the average exam mark for every student that studied online. In our example, the cells in column E identify whether or not the student studied online. If the student is an online student, the cells in column E also include the student’s country.

Since we are only interested in knowing whether a student is online or not, we can ignore the country part of the cell content. To do this, we use the wildcard asterisk character (*) in the following formula:

=AVERAGEIFS(B3:B51,E3:E51,”=Online*”)

As you’ve probably guessed, we insert the formula into cell H3.

AVERAGESIFS Example Online Students

The average mark for online students is 69.95 as you can see from the image above. Again, let’s take a closer look at the formula:

  • average_range – the average_range parameter is the range of cells B3:B51 containing the student’s exam results.
  • criteria_range1 – the criteria_range1 parameter is the range of cells E3:E51 that contains the student’s location of study. This can either be the campus or online. If online, the country of the student is also included.
  • criteria1 – the criteria1 parameter uses the equal to (=) comparison operator and the wildcard asterisk character (*) to test if the student’s location is online using the expression “=Online*”. This expression will match any text string that starts with the text Online because the asterisk wild card character (*) is used to represent any text.

For more information on wildcard characters, check out my post by clicking here.

Multiple Criteria Using Text and Numbers

We’ve already covered using multiple criteria, criteria_range pairs in our first example. This further example demonstrates using a mixture of text and numeric criteria. In this example, we want to get the average exam result for females over the age of 30 using the following formula:

=AVERAGEIFS(B3:B51,C3:C51,”>30″,D3:D51,”=Female”)

Insert the formula into cell H3:

AVERAGEIFS Female Students Over 30

  • average_range – the average_range parameter is the range of cells B3:B51 containing the student’s exam results.
  • criteria_range1 – the criteria_range1 parameter is the range of cells C3:C51 that contains the student ages.
  • criteria1 – the criteria1 parameter uses the greater than (>) comparison operator to test whether or not the student is more than 30 years old, using the expression “>30”.
  • criteria_range2 – the criteria_range2 parameter is the range of cells D3:D51, which contains the students gender.
  • criteria2 – the criteria2 parameter uses the equal to (=) comparison operator to test whether or not the student is female, using the expression “=Female”.

Using Named Ranges

A personal favourite of mine is to use Named Ranges; I think they make formulas easier to read and maintain. First, we create three named ranges, ExamResult, Age, and Gender.

To create the named range ExamResult, select cells B3:B51; click in the Name Box, type ExamResult, and press enter.

AVERAGESIFS Create Named Range ExamResult

Next, we create the named range Age. Select cells C3:C51; click in the Name Box, type Age and press enter.

AVERAGESIFS Create Named Range Age

Finally, we create the last named range, Gender. Select cells D3:D51; click in the Name Box, type “Gender” and press enter.

AVERAGESIFS Create Named Range Gender

Now, we add the named ranges to our formulas:

=AVERAGEIFS(ExamResult,Age,”>30″,Gender,”=Female”)

Like before, we insert the formula into cell H3:

AVERAGESIFS Example using Named Ranges

The average exam result for females over 30 is 68.875.

  • average_range – the average_range parameter is the named range ExamResults containing the student’s exam results.
  • criteria_range1 – the criteria_range1 parameter is the named range Age that contains the student ages.
  • criteria1 – the criteria1 parameter uses the greater than (>) comparison operator to test if the student is greater than 30 years old using the expression “>30.”
  • criteria_range2 – the criteria_range2 parameter is the named range Gender that contains the students’ genders.
  • criteria2 – the criteria2 parameter uses the equal to (=) comparison operator to test if the student is female using the expression “=Female”.

For more information on named ranges, check out my post by clicking here.

Common Errors

There are a few instances when the AVERAGEIFS function will return the #DIV/0! error message.

If the average_range parameter range of cells is blank or contain text strings, the AVERAGEIFS function will return the #DIV/0! error message.

AVERAGESIFS Example Error Message Blank Cells in the average_range parameter

If a cell meets the criteria in the criteria, criteria_range parameter pairs but the value in the average_range isn’t a valid numeric value, the AVERAGEIFS function returns the #DIV/0! error message.

AVERAGESIFS Example Error Message Invalid Cells values in the average_range parameter

If no cells meet the criteria in the criteria, criteria_range parameter pairs, the AVERAGEIFS function returns the #DIV/0! error message.

AVERAGESIFS Example Error Message No Cells Match The Criteria

For more information on Excel error messages, check out my post by clicking here.

Wrap-up

Okay, that’s it for this blog post. The AVERAGEIFS function can be extremely useful when you want to calculate the average of a range of cells that meets criteria specified by you. You’ve seen how to use the AVERAGEIFS function with multiple criteria, text and numerical criteria, and how to use the AVERAGEIFS function with named ranges and wildcard characters. You’ve also seen what can cause the AVERAGEIFS function to return an error message.

Remember that the AVERAGEIFS function calculates an arithmetic mean average.

That’s it for this post.  If you’d like a copy of the spreadsheet used in this post, please sign up for our newsletter.

For tools and resources to help you learn Excel, check out my resources page.

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit