Learn all about the AVERAGEIFS function in Google Sheets here. I am detailing every aspect of Averageifs multiple criteria function in Google Sheets that may come to you very useful in real-life use.
Averageifs is a Statistical function but with a logical flavor. It’s a statistical function as you can use it to find the average of a range. But at the same time, you can apply logical conditions in calculating the average.
The Syntax of Averageifs Multiple Criteria Function:
AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
Here the function argument average_range is the range to find an average. Criterion1 is the first criteria, condition or pattern to test in the criteria_range1. If you want to use only a single criterion you can use the Averageif function.
Here you can use multiple criteria and of course multiple criteria range. I think I can better convey to you what I want to say with a few examples.
How to Use Averageifs Multiple Criteria Function in Google Sheets
To make you understand the use of different criteria in Averagifs like date, text, numeric, comparison operators, in each example below, I’ve chosen different criteria.
1. Averageifs in Single Criteria Similar to Averageif
When I say Averageifs Multiple Criteria function, it doesn’t mean that you can only use multiple criteria in Averageifs. On the contrary, you can use single criteria too similar to Averageif.
Text criteria in Averagifs in Google Sheets
In the below example formula, I find the average age of females in a table. Unlike Averageif, in Averageifs the average range comes first. That is column C here.
Then how to use Averageif in the above example. See the formula below.
=averageif(B2:B9,"F",C2:C9)
The difference between Averageif and Averageifs in Google Sheets is in the criteria used. In Averageif, you can only use one single criterion whereas in Average ifs you can use multiple criteria.
Another difference is the positioning of arguments. As you can see clearly above, in Averageif the criteria range comes first, but in Averageifs it’s the Average range.
2. Averageifs with Multiple Condition
This example justifies the use of Averageifs Multiple Criteria functions in Google Sheets. The below example shows you how to use multiple criteria in Averageifs in Google Sheets. Also, I’ve used the comparison operator here.
3. Date as Criteria in Averageifs
When you want to use a date as the criterion in Averageifs, simply follow the below example. The date should be used in thedate(yyyy,mm,dd)
format.
Note: The average range can be number or date.
Can we Use OR criteria in Averageifs?
Certainly not. In some situations, you may want to find average based on multiple criteria in the same column. But you are not permitted to use logical OR in Averageifs. So the solutions are either use an Average + Filter function combo or Query. The versatility of Query functions always amazes me!
Find Average in Google Sheets When the Multiple Conditions Fall in the Same Column
Here I am finding the average age of both Male and Females. I just want to demonstrate how to use multiple conditions in the same column in Google Sheets.
I’ve just picked a sample data for this example which may or may not make any sense in real-life use.
Formula 1:
=average(filter(A2:C9,(B2:B9="F")+(B2:B9="M")))
Formula 2:
=query(A2:C9,"Select Avg(C) where B='F' or B='M'")
I think the above examples are enough to learn the use of Averageifs Multiple Criteria Function in Google Sheets. Hope you have enjoyed the stay!