IF functions in Google Sheets are some of the most powerful functions when it comes to drawing conclusions from your raw data. Once you understand one of them, you can extrapolate how to work with all of them. IFS are a little different as they can work with multiple conditions. This guide will walk you through everything you need to know about the AVERAGEIFS Google Sheets function to get started with it.
What Is the Google Sheets AVERAGEIFS Function
The Google Sheets AVERAGEIFS Function allows you to find the average of a cell range that meets certain specified criteria by the user. You can utilize the AVERAGEIFS formula in your spreadsheet to find data, such as cells that contain a specific color or the marks of all the girls in a class.
AVERAGEIFS specifies the cell range containing the data you wish to average and the cell range that includes the criteria you want to compare against. You will also need to specify the requirements that the cells need to fulfill. You can calculate three different types of averages using the averageifs Google Sheets formula. These are the minimum, maximum, and average.
Syntax for the AVERAGEIFS Formula Google Sheets
Before we look at the AVERAGEIFS function in action, let’s look at the formula and the parameters used in the formula. The formula for average ifs is:
=AVERAGEIFS(avg_range, crit_range1, criterion1, crit_range2, criterion2)
The formula requires three parameters to work. These are:
- avg_range: this parameter is the address of the cell range which you want the Google Sheet AVERAGEIFS formula to average. This is slightly different from the Google Sheets average if multiple criteria formula, as this is the first parameter in the AVERAGEIFS formula.
- crit_range1: this is the cell range the formula will check against for the criterion1 parameter.
- criterion1: this parameter defines the condition to qualify the cells in the crit_range1 parameter for averaging. You can choose from six different comparison operators. While the first two operators can be used for text and numbers, you only compare numbers with the last four operators. These operators are:
- Equal – ( = )
- Less Than – (<)
- Less Than / Equal – ( <= )
- Greater Than – ( > )
- Greater Than / Equal – ( >= )
- Not Equal – ( <> )
- crit_rangeN: this optional parameter allows you to add additional cell ranges that you want to check the criteria against.
- criterion: this optional parameter in the range for the crit_rangeN parameter allows you to define the condition to check for.
Related Reading: The 3 Google Sheets Wildcards and How to Use Them
AVERAGEIFS Google Sheets Example
Now that we know the syntax for the Google Sheets average ifs formula, let’s take a look at the formula in action.
AVERAGEIFS With Single Criteria
In this example, we use the AVERAGEIFS formula to find the average of the marks obtained by the girls and boys in the class to compare who obtained the highest average marks.
Here is how to use AVERAGEIFS with single criteria:
- Click on the cell where you wish to enter the formula and enter the starting part of the AVERAGEIFS formula, which is =AVERAGEIFS(
- Now, enter the avg_range parameter. In this example, it is C2:C11. Enter a comma symbol to separate the parameters.
- Enter the crit_range parameter, which is B2:B11. Add another comma.
- Finally, we will add the criterion, which is “F”. Make sure to add quotation marks.
- Add a closing bracket to finish the formula and press Enter to execute it.
AVERAGEIFS Using Multiple Conditions
In this example, we are going to have multiple conditions. Consider the data from the previous example. Here, we not only want to find out the averages of the girls in the class, but we also want to find out the average marks of the girls who scored above 80.
To do this, we use the same formula from the previous example. However, we will add additional four and five parameters, allowing us to add the new condition. You can do this by adding a comma and adding the new parameter.
Related Reading: 2 Easy Ways to Calculate Weighted Average in Google Sheets
Frequently Asked Questions
How Does the AVERAGEIFS Formula Work?
You can use the AVERAGEIFS function to find the average of a range of cells that meet certain user-specified criteria. AVERAGEIFS specifies a range of cells containing the data to average and a range of cells containing the criteria to compare. You also need to specify the criteria that the cells must meet.
What Is the Difference Between AVERAGEIF and AVERAGEIFS?
As their name may imply, both functions essentially do the same thing. However, the main difference is that the AVERAGEIF can only handle a single condition, while the AVERAGEIFS formula allows you to add multiple criteria.
Wrapping Up the AVERAGEIFS Google Sheets Function Guide
You should now understand the basics of the AVERAGEIFS Google Sheets function. This guide should also help you understand how to use most other IFS calculations too. If you’re still having trouble, check out some of the related content below to learn more.
- Google Sheets IFS Function Guide
- How to Count Cells with Specific Text In Google Sheets
- Google Sheets NOT Function
- Count Cells IF NOT Blank (Non-Empty cells) in Google Sheets
- FILTER Function in Google Sheets
- How to VLOOKUP from Another Sheet in Google Sheets?
- How to Use IMPORTRANGE Function in Google Sheets
- How to Use SUMIF function in Google Sheets?
- How to VLOOKUP Multiple Criteria in Google Sheets