The IF function in Google Sheets is one of the most powerful functions for drawing conclusions from raw data. Once you understand one of them, you can guess how to handle them all. IFS is a little different as it works in multiple conditions. This guide will explain everything you need to know to get started with the AVERAGEIFS Google Sheets function.
Contents
What Is the Google Sheets AVERAGEIFS Function
The AVERAGEIFS function in Google Sheets allows you to find the average of a range of cells that meet certain criteria provided by the user. Using her AVERAGEIFS formula in a spreadsheet, you can search for data such as cells containing a particular color or marks for all girls in a class.
AVERAGEIFS specifies the cell range that contains the data to average and the cell range that contains the criteria to compare. You must also specify the requirements that the cell must meet. You can use the Averageifs Google Sheets formula to calculate three different types of averages. These are minimum, maximum and average values.
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 average if formula is:
=AVERAGEIFS(avg_range, crit_range1, criterion1, crit_range2, criterion2)
This formula requires three parameters to work these are:
- avg_range: This parameter is the address of the range of cells to be averaged by the AVERAGEIFS formula in Google Sheets. This is the first parameter in the AVERAGEIFS formula, so it is slightly different from the multi-criteria average formula in Google Sheets.
- crit_range1: This is the cell range that the formula checks against the criterion1 parameter.
- criterion1: This parameter defines the criteria for including cells within the crit_range1 parameter for averaging. You can choose from six different comparison operators. The first two operators can be used with text and numbers, but only the last four operators can compare numbers. These operators are:
- equal to – (=)
- Less than – (<)
- Less than / equal to – (<=)
- Greater than – (>)
- Greater than / Equal to – (>=)
- Not Equal – (<>)
- crit_rangeN: This optional parameter allows you to add a cell range to check the condition.
- criterion: This optional parameter within the crit_rangeN parameter allows you to define a condition to check.
Related article: 3 wildcards in Google Sheets and how to use them
AVERAGEIFS Google Sheets Example
Now that we know the syntax of the average ifs formula in Google Sheets, let’s see the formula in action.
AVERAGEIFS With Single Criteria
This example uses the AVERAGEIFS formula to average the scores obtained by the girls and boys in the class and compare who gets the highest average score.
Here’s how to use AVERAGEIFS with a single criterion:
- Click in the cell where you want to enter the formula and click the beginning of the AVERAGEIFS formula, =AVERAGEIFS(
- now enter the avg_range parameter. In this example, C2:C11. Enter commas to separate parameters.
- Enter B2:B11 as the Crit_range parameter. Add another comma.
- Finally, add a criterion called ‘F’. Be sure to add quotation marks.
- Add a closing parenthesis to end the formula and press Enter to run it.
AVERAGEIFS Using Multiple Conditions
This example sets multiple conditions. Consider the data from the previous example. Not only do we want to know the average score of the girls in our class, but we also want to know the average score of the girls who scored above 80.
To do this, we use the same formula as in the previous example. But let’s add four and five more parameters so that we can add new conditions. To do this, add a comma to add a new parameter.
Related article: 2 Easy Ways to Calculate Weighted Averages in Google Sheets
Frequently Asked Questions
How Does the AVERAGEIFS Formula Work?
The AVERAGEIFS function allows you to find the average of a range of cells that meet certain criteria you specify. AVERAGEIFS specifies the range of cells containing the data to be averaged and the range of cells containing the criteria to be compared. You must also specify the criteria that the cells must meet.
What Is the Difference Between AVERAGEIF and AVERAGEIFS?
As the names suggest, both functions essentially do the same thing. The main difference, however, is that AVERAGEIF can only handle one condition, while the AVERAGEIFS formula allows you to add multiple conditions.
Wrapping Up the AVERAGEIFS Google Sheets Function Guide
You should now have a basic understanding of the AVERAGEIFS Google Sheets function. This guide will also help you understand how to use most other IFS calculations. If you’re still having trouble, see the related content below for more information.
