Check Sheet Logo
Check Sheet

AVERAGEIFS

The AVERAGEIFS formula calculates the average of a range of cells that meet multiple criteria. It is most commonly used to find the average of a range of values that meet specific conditions. The formula takes one required argument (average_range) and any number of optional argument pairs (criteria_range, criterion).

Usage

Use the AVERAGEIFS formula with the syntax shown below, it has 3 required parameters and 2 optional parameters:

=AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
Parameters:
  1. average_range (required):
    The range of cells to calculate the average of.
  2. criteria_range1 (required):
    The range of cells to apply the first criterion to.
  3. criterion1 (required):
    The criterion to apply to the first criteria range.
  4. criteria_range2 (optional):
    An additional range of cells to apply a second criterion to.
  5. criterion2 (optional):
    The criterion to apply to the second criteria range.

Examples

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

Finding the average of a range of cells that meet a specific condition

Suppose you have a list of students and their grades, and you want to find the average grade for students who scored above 90. You can use AVERAGEIFS to calculate this by specifying the grade range as the average range and the score range as the criteria range with the criterion '>90'.

Calculating the average of a range of cells that meet multiple conditions

You can use AVERAGEIFS to calculate the average of a range of cells that meet multiple criteria. For example, you can find the average sales for a specific product in a specific region by specifying the sales range as the average range, the product range as the first criteria range, and the region range as the second criteria range.

Common Mistakes

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

Incorrect criteria range or criterion

One of the criteria ranges or criteria is incorrect or misspelled. Double-check the spelling and make sure the criteria are correct.

Using non-adjacent ranges for criteria

All the criteria ranges should be adjacent to each other. If they are not, you may get unexpected results or an error. Use the CONCATENATE function to combine non-adjacent ranges into one range.

Forgetting to add average_range

The AVERAGEIFS function requires an average range to calculate the average. Make sure you have included this range in your formula.

Related Formulas

The following functions are similar to AVERAGEIFS 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.

  • SUMIFS

    The SUMIFS formula is a function in Google Sheets that adds the values in a specified range based on multiple criteria. It is most commonly used to sum data that meets specific criteria, such as summing sales for a particular month by region or summing expenses for a specific category and date range.

  • COUNTIFS

    The COUNTIFS function counts the number of cells in a range that meet multiple criteria. It is commonly used to analyze data sets and filter out specific information based on selected criteria.

Learn More

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