Skip to Content

How to Use the AVERAGEIFS Function in Google Sheets

The AVERAGEIFS function in Google Sheets is very similar to the AVERAGEIF function except there is one key difference.

AVERAGEIF only allows you to check for one criterion, AVERAGEIFS on the other hand, allows you to check multiple criteria.

In this tutorial, I will show you how to use the AVERAGEIFS function in Google Sheets.

Table of Contents

AVERAGEIFS Syntax

The AVERAGEIFS function will return the average of a range depending on multiple criteria.

The syntax of this function is:

=AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])

  • average_range – this is the range to average
  • criteria_range1 – the range to check for criterion1 against
  • criterion1 – the condition or test to check criteria_range1 for
  • criteria_range2, criterion2, – These arguments are optional. You can add additional ranges and criteria here

For your criterion there are six kinds of operators that you can end up using in your formula:

  • Equals (=)
  • Not equal to (<>)
  • Greater than (>)
  • Greater than or equal to (>=)
  • Less than (<)
  • Less than or equal to (<=)

Your criterion will have to be inside quotation marks if you are using a comparison operator or text as your criterion. If your criterion is only a numeric value without a comparison operator you will not need quotation marks for it.

How to Use AVERAGEIFS

To get a better understanding of how to use this function in a spreadsheet, let’s take a look at an example.

In this example, I have a table with sales information and I want to find the average revenue for products that meet multiple criteria. I only want to find the average revenue for Clothing items that have more than 5 sales.

Here is how this formula would be set up in a spreadsheet:

AVERAGEIFS example

In the example my formula is:

=AVERAGEIFS(E2:E8,B2:B8,”Clothing”,D2:D8,”>5″)

Let’s break his formula down step by step so we can see what each argument does:

  • E2:E8 – in this example, this is my average_range. This is the range that I want to return the average for since I’m interested in returning average revenue for lines that meet my criteria
  • B2:B8 – the next argument in my function is the critera_range1. Since I only want to return Clothing items in my average, I have to put this as my criteria_range1 so I can apply a test to it in the next argument
  • “Clothing” – this is my criterion1. This means that I will only average out the corresponding cell in E2:E8, where B2:B8 is equal to “Clothing”. If you are using text in your criterion it needs to be inside quotation marks like mine is in this formula
  • D2:D8 – this is my critera_range2. I also want to run a test on this range and only average values that meet my second criterion
  • “>5” – this is my criterion2. I have this set so that the corresponding cell in E2:E8 will only be used to calculate the average if D2:D8 is greater than 5. Any time you are using a comparison operator in your argument you have to make sure you are placing it inside quotation marks

Closing Thoughts

The AVERAGEIFS function is a very powerful formula for calculating averages only if the values meet certain criteria. With this function, you can set up multiple criteria that your data must pass in order to be used in the average.

If you are regularly calculating this kind of data in your spreadsheet, this function will certainly save you a lot of time.

More Google Sheets Tutorials:
How to Do Multiple IF Statements
How to Find Quartiles
How to Use the RANK Function