How to use AVERAGEIF function in Google Sheets

By SpreadCheaters
Page last updated: 19/04/2023 | Next review date: 19/04/2025
close

Author bio


SpreadCheaters
View More north_east

If you are looking for Microsoft Excel’s online replacement then Google Sheets is the next best choice. It has almost all the functionalities in terms of functions and formulas as Excel does have. Google Sheets provide a huge variety of formulas for mathematical and statistical calculations.  

In this article, we are going to learn how to use a very handy function AVERAGEIF in Google Sheets. To use this function, we need to understand its syntax first. So, let’s take a look at the syntax of AVERAGEIF

AVERAGEIF(criteria_range, criterion, [average_range])

criteria_range

The range to check against ‘criterion’.

criterion

The pattern or test to apply to ‘criteria_range’. The criteria condition has to be written inside “ ”. Otherwise, you will get an error.

average_range – [optional]

The range to average. If not included, ‘criteria_range’ is used for the average instead.

AVERAGEIF can be used in a variety of ways however, we’ll discuss the following cases in this tutorial.

  • AVERAGEIF with text criteria
  • AVERAGEIF with numeric criteria
  • AVERAGEIF with date criteria

Let’s discuss all these one by one in the following steps.

Case 1 – AVERAGEIF with text criteria

We have a dataset of sales of different items and we’ll find out the average of sales if the item is “Furniture”. Let’s see how it can be done in the following step.

Step 1 – Implement AVERAGEIF formula with Text criteria

  • To calculate the average of furniture sales we’ll use the following formula

=AVERAGEIF(A2:A12, “Furniture”, D2:D12)

Case 2 – AVERAGEIF with numeric criteria

We have a dataset of sales of different items and we’ll find out the average of sales of all those items whose sold quantity is “>30”. Let’s see how it can be done in the following step.

Step 1 – Implement AVERAGEIF formula with number criteria

  • To calculate the average of furniture sales we’ll use the following formula

=AVERAGEIF(C2:C12, “>30”, D2:D12)

Case 3 – AVERAGEIF with Date criteria

We have a dataset of sales of different items and we’ll find out the average of sales of all those items which are sold on or after a specific date i.e., “08/11/2022”. 

Step 1 – Implement AVERAGEIF formula with number criteria

  • To calculate the average of furniture sales we’ll use the following formula

=AVERAGEIF(C2:C12, “>=08/11/2022”, E2:E12)