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.

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:

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], …)**.

Parameter | Description | |
---|---|---|

average_range | the average_range parameter is the range of cells containing the numbers to average. This parameter is required. | |

criteria_range1 | the criteria_range1 parameter is the range of values that is evaluated against the criteria1 parameter. This parameter is required. | |

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

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

Operator | Example | Description |
---|---|---|

> | >10 | greater than a specified value, in this example greater than 10 |

< | <10 | less than a specified value , in this example less than 10 |

= | 10 | equal to a specified value , in this example equal to 10 |

<> | <>10 | not equal to a specified value, in this example not equal to 10 |

>= | >=10 | greater than or equal to a specified value, in this example greater than or equal to 10 |

<= | <=10 | less 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**.

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.”**- c
**riteria_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**.

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

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

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

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

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

Now, we add the named ranges to our formulas:

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

Like before, we insert the formula into cell **H3**:

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.

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.

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

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.

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