Use AVERAGEIFS Function in Google Sheets | 3-min Guide

Use AVERAGEIFS Function in Google Sheets | 3-min Guide
Reading Time: 4 minutes
Using AVERAGEIFS Function in Google Sheets

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.

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. 

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.

Number of subjects failed in total marks obtained by students in a class
Fig 1: Example Data | AverageIfs function in Google Sheets

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. 

Calculating average marks of students who failed in 0 subjects
Fig 2: Basic use of AVERAGEIFS Function in Google Sheets

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″)

Calculating average marks of students if their total marks are greater than 50
Fig 3: Using AVERAGEIFS Function in Google Sheets with logical conditions

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)

Using AVERAGEIFS Function with dynamic values
Fig 4: Using AVERAGEIFS Function in Google Sheets with dynamic values

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. 

Calculating average marks of students if their total marks are greater than 50 and number of subjects failed are 0
Fig 5: Using AVERAGEIFS Function in Google Sheets with multiple conditions

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. 

Calculating average marks of students if their total marks are greater than 50 and number of subjects failed are 0 or number of subjects failed are 2
Fig 6: Using AVERAGEIFS Function in Google Sheets with OR condition

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.

Calculating average marks if the total marks column of a student is empty
Fig 7: Using AVERAGEIFS Function in Google Sheets with empty and non-empty cells

See Also

Google Sheets: SUMIFS Function | Quick Guide: Learn how to use the SUMIFS Function in Google Sheets.