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.
Frequently Asked Questions
How do I use the AVERAGEIFS function in Google Sheets?
To use the AVERAGEIFS function in Google Sheets, specify the average range, criteria range(s), and criteria for which the average needs to be calculated.
What is the difference between AVERAGEIF and AVERAGEIFS in Google Sheets?
AVERAGEIF calculates the average based on a single criterion, while AVERAGEIFS can calculate the average using multiple conditions in Google Sheets.
Can I use multiple criteria with AVERAGEIFS in Google Sheets?
Yes, you can use multiple criteria with the AVERAGEIFS function in Google Sheets to filter data and calculate the average based on those conditions.
What is the syntax for the AVERAGEIFS function in Google Sheets?
The syntax for AVERAGEIFS in Google Sheets is AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, ...], [criterion2, ...]).
Which operators can be used with AVERAGEIFS in Google Sheets?
Operators like =, <>, >, <, <=, >= can be used with AVERAGEIFS in Google Sheets to compare values and set conditions for calculating the average.
How many types of comparison operations can be used with AVERAGEIFS in Google Sheets?
There are six types of comparison operations that can be used with AVERAGEIFS in Google Sheets, including =, <>, >, <, <=, and >=.
How does AVERAGEIFS help in filtering data in Google Sheets?
AVERAGEIFS helps in filtering data in Google Sheets by allowing users to set multiple conditions to calculate the average based on specific criteria.
Is AVERAGEIFS function helpful for analyzing data in Google Sheets?
Yes, the AVERAGEIFS function in Google Sheets is useful for analyzing data as it provides a way to calculate averages based on multiple criteria, enabling more detailed analysis.