Check Sheet Logo
Check Sheet

AVERAGEIF

The AVERAGEIF function calculates the average of a range of cells that meet a specified criteria. It is commonly used when working with large datasets to quickly calculate the average of a subset of data. The function takes a range of cells to evaluate (criteria_range), a string or value to compare against (criterion), and an optional range of cells to average (average_range). If the average_range is not specified, the function will use the same range as the criteria_range.

Usage

Use the AVERAGEIF formula with the syntax shown below, it has 2 required parameters and 1 optional parameter:

=AVERAGEIF(criteria_range, criterion, [average_range])
Parameters:
  1. criteria_range (required):
    The range of cells to evaluate against the criterion.
  2. criterion (required):
    The string or value to compare against.
  3. average_range (optional):
    The range of cells to average. If not specified, the function will use the same range as the criteria_range.

Examples

Here are a few example use cases that explain how to use the AVERAGEIF formula in Google Sheets.

Calculate the average of all sales over $1000

Use AVERAGEIF to calculate the average of all sales over $1000 in a sales report.

Calculate the average rating for all movies released in 2020

Use AVERAGEIF to calculate the average rating for all movies released in 2020 in a movie database.

Calculate the average score for a student's top 3 test results

Use AVERAGEIF to calculate the average score for a student's top 3 test results in a school grading system.

Common Mistakes

AVERAGEIF not working? Here are some common mistakes people make when using the AVERAGEIF Google Sheets Formula:

Using incorrect or non-matching ranges for criteria and average

One common mistake is using different ranges for the criteria_range and average_range parameters, or using ranges that do not match in size or position. This will result in errors or incorrect calculations. Double-check that the ranges you are using are correct and match in size and position.

Forgetting to enclose text criteria in quotes

If your criterion is a text value, make sure to enclose it in quotes. Forgetting to do so will cause an error or result in incorrect calculations. Always enclose text criteria in quotes to ensure accurate results.

Using incorrect logical operators in criteria

Make sure to use the correct logical operators (such as >, <, >=, <=, =) in your criteria. Using the wrong operator or using it incorrectly can result in errors or incorrect calculations. Double-check your criteria to ensure you are using the correct operators.

Not including the average_range parameter

If you forget to include the optional average_range parameter, the formula will only count the number of cells that meet the criteria and return a value of 0. Make sure to include the correct range for average_range to get the desired results.

Using the wrong data type for average_range

The average_range parameter must contain numeric values or cell references. If you include text values, the formula will return an error. Make sure to use the correct data type for the values in your average_range parameter.

Related Formulas

The following functions are similar to AVERAGEIF or are often used with it in a formula:

  • AVERAGE

    The AVERAGE function calculates the average (arithmetic mean) of the values passed to it. It is commonly used to find the average of a range of cells containing numerical data.

  • SUMIF

    The SUMIF formula is used to add up values in a range that meet a specific criterion. It can be used to sum values based on text, numbers, or dates. The formula is most commonly used in financial analysis, budgeting, and data analysis.

  • COUNTIF

    The COUNTIF formula counts the number of cells within a specified range that meet a certain criterion. This formula is commonly used to count cells that meet a specific condition or criteria.

Learn More

You can learn more about the AVERAGEIF Google Sheets function on Google Support.