In this article:

May 8, 2024

The AVERAGEIF function in Google Sheets calculates the average of numbers within a range that meet a given set of criteria—combining the AVERAGE and IF functions.

=AVERAGEIF(criteria_range, criterion, [average_range])

Where:

**criteria_range=**

the data to which the criterion will be applied.

**criterion=**

the condition—a number, text, date, or comparison operator—in the criteria range you wish to specify.

**[average_range]=**

an optional argument, the average range indicates the numeric values you wish to average. If left blank, the average range defaults to the criteria range.

When the criterion is set as text, the function is a great way to calculate the average of numbers associated with a particular word or group of words.

Let’s say you have a sheet identifying the average monthly sales for a set of businesses within a particular area. If you want to calculate the total average monthly sales for all the businesses in a given category—let’s say automotive—this function will come in handy.

With a small range, you can simply select the cells with your cursor. For a larger one, it will be easiest to just type it inside the function.

When you’re using text as the criterion, you must place it in double quotation marks (“”) within the function. If you’re calculating the monthly sales for all automotive businesses, enter “Automotive.”

When your criterion is text, you’ll need to use the optional average range address reference to indicate the cells whose values you want to average.

The steps for using this function are a little different when the criterion is a number.

Let’s say a group of children between the ages of 8 and 10 were given a test, and you want to calculate the average score for the 8-year-olds.

In this instance, select B2 through B16, to identify the Age column as the range.

When the criterion is a number value, simply enter the digit as the next argument—in this case, 8.

Here you would select C2 through C16 to average the relevant data in the test score column.

The function can also be used with the criterion set as a date.

Say, for example, you want to use the formula to calculate the average number of units sold per customer on June 8, 2022.

When you’re working with a date, enter the value in double quotation marks, as you would with text.

Finally, you can also calculate an average for a criterion determined by six comparison operators:

- Equals (=)
- Not equal to (<>)
- Greater than (>)
- Greater than or equal to (>=)
- Less than (<)
- Less than or equal to (<=)

Let’s say you’re comparing the nutritional info of different snacks and wish to determine the average grams of protein for the snacks with more than 100 calories.

As with text and dates, criterion involving comparison operators must be entered surrounded by double quotation marks.

What if I have multiple multiple rangers or multiple criteria?

Google Sheets allows you to calculate the average for multiple ranges, criteria, and/or average ranges. To do so, however, you must use a different function with a distinct syntax: AVERAGEIFS.

IF: Allows you to check for specific conditions across a dataset

SUMIF: Use to sum numbers if they meet a certain condition..

SUMIFS: Sums data from cells that meet multiple criteria

COUNTIF: count data if it fulfils certain criteria

COUNTIFS: Count data that fulfils two or more criteria.

COUNTIF Not Null**: **Count cells if they contain data

IFS**: **IFS is a more elegant way to evaluate data against multiple criteria.

IFERROR**:** Allows you to output values when an Error in a formula occurs

IF THEN: Allows you tor write statements that use IF X Then Y Logic

IF AND**:** Combines the functionality of the IF and AND functions

IF Else**:** Set conditions that give an output depending on whether a given condition is fulfilled or not

Multiple IF Statements: How to chain multiple statements together.

IF OR: Combines the functionality of the IF and OR functions

IF Contains: used to look for cells that contain a certain string as its value

If you want to learn how to create a Google Sheets address book template, we also suggest checking out our detailed guide.

Get Google Sheets productivity and automation tips delivered straight to your inbox

We'll email you 1-3 times a week — and never share your information.

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->