The AVERAGEIFS() function in Google Sheets is used to find the average of the numerical values in a given data according to certain multiple conditions. It helps in filtering the data on the basis of more than one condition for the average. In this guide, we will learn how to use the AVERAGEIFS() function in Google Sheets.
How to Use AVERAGEIFS Function in Google Sheets?
The AVERAGEIFS() function works similarly to the AVERAGEIF() function in Google Sheets, but the key difference is that the AVERAGEIF() function calculates the average of the values for single criteria and the AVERAGEIFS() function calculates the average over multiple conditions.
The syntax used for the AVERAGEIFS() function in Google Sheets is as follows:
AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, ...], [criterion2, ...])Where,
average_range is the argument for the values for which we want to calculate the average value.
critera_range is the range for which the formula searches to check against criterion1.
criterion1 is the condition under which the average has to be calculated.
critera_range2 is an optional argument used for the additional range to check against criterion2.
There are six types of operations we used for the comparison of the values in the formula. Two of them can be used for both the numerical values and text inputs are as follows:
| Operator | Meaning |
|---|---|
| = | The right side is equal to the left side. |
| <> | The right and left sides are not equal. |
Below are the operators which can only be used to compare the numerical values in the formula:
| Operator | Meaning |
|---|---|
| > | The left side is greater than the right side. |
| < | The left side is greater than the right side. |
| <= | The left side is greater than and equal to the right side. |
| >= | The left side is less than and equal to the right side. |
To better understand the use of the AVERAGEIFS() function in Google Sheets, consider the following examples.
Example 1: Apply AVERAGEIFS() Function for Single Criteria
Consider we have a test result of a class which includes both boys and girls. In case, we want to find the average score obtained by boys, we will use AVERAGEIFS() Function as follows.
Step 1: Click on an empty cell and type AVERAGE. Google Sheet’s suggested formulas will appear below this cell, click on the AVERAGEIFS function from them:
Step 2: Select the range as the average_range argument in the function. In our example, C4:C19 is our range of data containing the scores obtained by the whole class, over which we want to calculate the average of the scores obtained by the boys:
Step 3: Select the critera_range. In our example, B4:B19 is the criteria_range:
Step 4: Select criterion1 from the critera_range. As we want to calculate the average of the scores obtained by the boys, we will select a cell that specifies the boys in the criteria_range. Click only one cell that is associated with the boys. In our example, the cell that contains M are associated to boys, so we will select the cell that contains M:
We can also write M in commas as follows, instead of selecting the cell that contains this letter:
Step 5: Now close the bracket and press Enter. We will get the average of the scores obtained by all the boys in the selected cell, where we applied the AVERAGEIFS() function:
Example 2: Apply AVERAGEIFS() Function for Multiple Criteria
In this example, will calculate the average of the scores obtained by boys, but for only those whose scores are above 25. Now consider the data given in the above example and follow the following steps.
Step 1: Select an empty cell and type the AVERAGEIFS() function as mentioned above. We will use the first three arguments the same as in the above example, for the averag_range, riteria_range and criterion1:
Step 2: Select the criteria_range2. In our example, the criteria range is C4:C19, which is the same as criteria_range1:
Step 3: Type the criterion2 as >25 in commas, since we want to calculate the average of the scores greater than 25 for the boys:
Step 4: Now close the bracket and press Enter, we will get the average of the scores for the boys whose scores are greater than 25:
Conclusion
With the use of the AVERAGEIFS() function, we can calculate the average of the values over multiple conditions. Click on an empty cell and type the =AVERAGEIFS and then select the data that contains the value over which we want to calculate the average as the average_range argument and then type the data range for the criteria_range argument to check against criterion argument. Now select the range of data for criteria_range2 to check against the criterion2.