What it does – Returns the average of a range of cells that fulfil a condition.
Syntax
=AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])average_range – Refers to the range of cells for which you want to calculate the average. criteria_range1 – This is the range of cells against which you will check criterion1.criterion1 – This is the condition against which the cells in criteria_range1 will be checked. criteria_range2, criterion2, ... – [ Optional] – These are any additional ranges and criteria that you want to check.
Sample Usage=AVERAGEIFS(C2:C12, C2:C12, ">50")
//Returns the average of items in the range C2:C12 whose values are greater than 50.
Objective
Learn how to use the AVERAGEIFS Function in Google Sheets.
Introduction
As the name suggests, the AVERAGEIFS Function in Google Sheets takes only those cells into consideration that fulfil some specified conditions and then calculates their average. The AVERAGEIFS function is very convenient to use in situations where you want to filter a range based on some condition and then calculate the average based on this filter.
You can also sum instead of averaging and for this you can refer to our guide on how to use SUMIFs function in Google Sheets.
If you’re confused about various Average-related formulas, you may find it useful to scan through various average formulae in Google Sheets.
Example Data
The spreadsheet below shows a list of students with the number of subjects they failed and the total marks obtained. This information will be used to illustrate all AVERAGEIFS function examples.
Basic Use
Now let’s say for the given data I want to calculate the average marks of those students who have failed in 0 subjects. I will make use of the following AVERAGEIFS Function formula to find the same.
=AVERAGEIFS(C2:C12,B2:B12,”0″)
The AVERAGEIFS Function returns the following output.
Using AVERAGEIFS Function with:-
Greater Than/Less Than/Equal to/Not Equal to conditions
In this example let us look at how we can calculate the average marks of students who have total marks greater than 50. I will make use of the greater than (>) logical operator and apply the following formula to calculate the same.
=AVERAGEIFS(C2:C12,C2:C12,”>50″)
Dynamic Values
Let’s suppose instead of fixing the value of total marks as 50 for comparison in the previous example, I want to keep it dynamic. So here I will pass the condition to the function by enclosing the logical operator (>) in “ “ and concatenating it with ampersand (&).
=AVERAGEIFS(C2:C12, C2:C12, “>”&C14)
Multiple Criteria (AND Condition)
This time let’s calculate the average marks of students who have total marks greater than 50 and have failed in 0 subjects. Since in this example we have to take two criterions into consideration, I will apply AND condition logic while using the AVERAGEIFS Function.
=AVERAGEIFS(C2:C12, C2:C12, “>50”, B2:B12, “0”)
This gives the following result.
OR Condition
In this case I want to calculate the average marks of students who have failed in 0 subjects and obtained total marks less than 50 or the average marks of students who have failed in 2 subjects. In such a case I have to use two AVERAGEIFS here. The following formula depicts this operation.
=AVERAGEIFS(C2:C12,C2:C12,”>50″,B2:B12,”0″) + AVERAGEIFS(C2:C12,B2:B12, “2”)
The figure shown below demonstrates the output returned by the AVERAGEIFS Function.
Empty and Non-Empty cells
We use the following conditions to sum the cells in a column depending on whether they are empty or not-
- “=” : Used to sum empty cells
- “ ” : Sums empty cells including zero length strings
- “<>” : Used to sum non-empty cells including zero length strings
Let’s suppose for some students the Total Marks column is empty. In such a case I want to find the average marks of only those students for which the Total Marks have been specified. I can use the following formula to do so.
=AVERAGEIFS(C2:C12,C2:C12,”<>”)
The following output is displayed.
See Also
Google Sheets: SUMIFS Function | Quick Guide: Learn how to use the SUMIFS Function in Google Sheets.