Skip to content

Learn the AVERAGEIFS Function

In one of our previous articles, we showed you how to use the AVERAGEIF function. In this article, we will go into how to use the AVERAGEIFS function.

The AVERAGEIFS Function

The AVERAGEIFS function allows you to calculate the arithmetic mean for cells based on several conditions.

For example, if you have a list of prices and would like to average only the prices greater than $10 and less than $20, then you would use the AVERAGEIFS function.

The AVERAGEIFS function also allows you to calculate the mean for cells in one range while evaluating the criteria against a different range or set of ranges. To understand this, let’s look at how the AVERAGEIFS function works.

How the AVERAGEIFS Function Works

The AVERAGEIFS function requires at least three arguments to work.

  • The average_range
  • The criteria_range
  • The criteria
=AVERAGEIFS(average_range, criteria_range, criteria)

The arithmetic mean is calculated for the average_range. The criteria_range is tested against the criteria. And finally, the criteria is what the criteria_range is evaluated against.

The AVERAGEIFS function will check to see which cells in the criteria_range meet the criteria. It will then average the cells in the average_range that correspond with the cells that meet the criteria.

Take a look at this example.

The AVERAGEIFS is looking for a value of “North” in the first range.

The AVERAGEIFS function then calculates the mean for the cells in the average_range that are in the same row (corresponding position) as those cells containing the value of “North.”

As mentioned before, the AVERAGEIFS function allows you to average the cells in a spreadsheet based on multiple conditions as well. It is not limited to testing only one criterion. For each additional criterion, there must be two more arguments – another criteria_range and another specified criteria.

=AVERAGEIFS(average_range, criteria_range_1, criteria_1, criteria_range_2, criteria_2, etc.)

How to Use the AVERAGEIFS Function

Here are two visual examples to help you better understand how to use the AVERAGEIFS function, here are two visual examples.

Example 1

The first example illustrates how to use the AVERAGEIFS function when testing two criteria.

The AVERAGEIFS function calculates the mean for the cells with a value greater than 10. But only if these cells correspond with the value of “North” in the adjacent range.

Example 2

The second example shows the AVERAGEIFS function when testing for three criteria.

The AVERAGEIFS function averages only the cells that meet the following criteria.

  • They correspond with cells containing the text value of “Blue”
  • They correspond with cells containing the letter “B”
  • They correspond with cells containing the value TRUE

And that is how you can use the AVERAGEIFS function to calculate the arithmetic mean for cells based on multiple criteria.

We hope you learned something new, and leave a comment below if you have any questions!

This Post Has One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top