AVERAGEIF in Google Sheets: Beginner’s Guide

Last updated November 29, 2021

One of the most commonly used functions in Google Sheets is AVERAGE. It lets you easily get the numerical mean in a set of data. While it is a very helpful function, it still has limitations.

The AVERAGEIF function in Google Sheets is just like AVERAGE, but a bit more complex. It allows you to get the average of the data that meets a specific criterion.

AVERAGEIF Google Sheets Syntax

The AVERAGEIF function in Google Sheets has the following syntax:

=AVERAGEIF(criteria_range, criterion,[average_range])

Let us break down its arguments.

  • criteria_range is the set of categories containing the specific criterion whose average will be taken.
  • criterion is the condition to be tested against the criteria_range. This can be in the form of number, text, date, or expression.
  • average_range is an optional argument. It contains the values to get the average of. If the average_range is not included in the formula, the average will be taken from the criteria_range.

AVERAGEIF Criterion in the Form of Number

Take a look at the image below. The spreadsheet contains the income report of two companies from multiple criteria. As an example, we will get the average income of Company 1.

income report of two companies from multiple criteria

1. To start, type the following in an empty cell:

=AVERAGEIF(
AVERAGEIF formula in Google Sheets

You’ll see that the syntax will appear on your screen upon typing the function. This happens for all functions in Google Sheets, so there’s no need to memorize the arguments of every function.

Next, we will select the criteria_range. The criteria_range in this example is the data under “Company”, which are Company 1 and 2.

2. Highlight the data under “Company”, cells C3 to C13.

2. Highlight the data under “Company”, cells C3 to C13.

3. Type a comma ( , ) to move on to the next argument.

3. Type a comma ( , ) to move on to the next argument.

You’ll see that as you type the comma after the argument, the next argument to be taken becomes highlighted, as shown in the image above.

This is a helpful feature in Google Sheets that lets you avoid confusion, especially when the function you are using contains many arguments.

We will now select the criterion. Our criterion in this example is “Company 1”.

4. Select any cell in the criteria_range that contains “1”.

Select any cell in the criteria_range that contains “1”.

Alternatively, you can type “1” instead of clicking on a cell. In this case, your current formula will be:

=AVERAGEIF(C3:13,1

5. Type a comma ( , ) to move on to the last argument.

Our last argument, average_range, is the data under “Amount”.

6. Highlight the data under “Amount”, cells D3 to D13.

Highlight the data under “Amount”

7. Type a closing parenthesis to complete the formula.

complete the formula.

8. Press Enter to get the average.

Average

That’s it! The average income of Company 1 will now show on your screen.

You can do the same steps for getting the average income of Company 2.

AVERAGEIF Criterion in the Form of Text

The criterion argument of the AVERAGEIF function can also be in the form of text. Let us use the previous example but this time, we will get the total income of both companies coming from “Sales”.

1. Type the function any empty cell:

=AVERAGEIF(
=AVERAGEIF(

Select the criteria_range. This time, our criteria_range will be the data under “Category.”

2. Highlight the data under “Category”, cells B3 to B13.

Highlight the data under “Category”

3. Type a comma ( , ) to move forward to the next argument.

The next argument is the criterion, which is in this case, “Sales”.

4. Select any cell in the criteria_range that contains “Sales”.

Select any cell in the criteria_range that contains “Sales”.

Like the first example, you can also type “Sales” instead of clicking on a cell. But, it’s important to take note of the quotation marks (“ ”). Your current formula will now look like this:

=AVERAGEIF(B3:B13,“Sales”
=AVERAGEIF(B3:B13,“Sales”

When typing texts or other characters aside from numbers in a formula, these quotation marks should be included for the formula to work properly. It will result in an error if there are no quotation marks. See the image below.

error

Here, I typed “Sales” without quotation marks. You will see that this results in an error. Also, take a look at the color of the word “Sales” from this image and the previous image. If the argument is written properly, the text will be colored green. Otherwise, it will be colored black.

5. Type a comma ( , ) to move on to the last argument.

Same as the previous example, our average_range is the data under “Amount”.

6. Highlight the data under “Amount”, cells D3 to D13.

Highlight the data under “Amount”, cells D3 to D13.

7. Type a closing parenthesis to complete the formula.

closing parenthesis

8. Press Enter to get the average.

Average

AVERAGEIF Criterion in the Form of Comparison Operators

Aside from numbers and texts, the criterion in the AVERAGEIF function can be in the form of comparison operators. Let us first review what are the comparison operators in Google Sheets.

Comparison Operators in Google Sheets

There are six comparison operators in Google Sheets.

  • Equal to ( = ) determines if the data on the right side is equal to the data on the left side.
  • Not Equal to ( <> ) compares if the data on the right side is not equal to the data on the left side.
  • Less than ( < ) determines if the data on the right side is less than the data on the left side.
  • Greater than ( > ) checks if the data on the right side is greater than the data on the left side.
  • Less than or Equal to ( <= ) determines if the data on the right side is less than or equal to the data on the left side.
  • Greater than or Equal to ( >= ) compares if the data on the right side is greater than or equal to the data on the left side.

Let’s take the previous data. In this example, our objective will be to find the average of the amounts less than 100,000. To start,

1. Type the function into any empty cell:

=AVERAGEIF(
=AVERAGEIF(

Unlike the previous examples, our criteria_range here will be the data under “Amount”.

2. Highlight the data under “Amount”, cells D3 to D13.

Highlight the data under “Amount”

3. Type a comma ( , ).

For our criterion, we will get the average of the amounts less than 100,000. To be able to write this inside the formula:

4. Type “ < 100,000 ”. Do not forget to include the quotation marks.

Type “ < 100,000 ”.

7. Type a closing parenthesis to complete the formula.

Type a closing parenthesis to complete the formula.

Notice that in this example, we have no data for the argument average_range. This is because the average will be taken from the criteria_range.

8. Press Enter to get the average.

Press Enter to get the average.

How Do I AVERAGEIF Multiple Ranges or AVERAGEIF Multiple Criteria?

Sometimes, we encounter spreadsheets with lots and lots of categories, and often, we might be tempted to use AVERAGEIF for multiple criteria. But, This is where the AVERAGEIFS function becomes helpful. This function is the same as AVERAGEIF, but it has more arguments. You can use this time-saving function to get the average of the data under multiple criteria.

What to Learn Next

In this article, you have learned how the AVERAGEIF function in Google Sheets works. Additionally, the use of the different criterion (number, text, and comparison operators) was discussed. You can use this function in calculating the average of the data given a criterion.

You can continue your spreadsheet mastery by applying what you’ve learned here to other IF functions such as SUMIF and COUNTIF.

Jake Wright

Jake Wright

Jake is a spreadsheet expert and content writer from New Zealand. He has a double Bachelor's Degree in Teaching and has been working in the education industry for over 11 years. His experience makes him adept at breaking down complex topics so that everyone is able to understand.