AVERAGEIF
TheAVERAGEIF
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.
- How to use
AVERAGEIF
formula? - Examples of using
AVERAGEIF
formula AVERAGEIF
formula not working?- Similar formulas to
AVERAGEIF
Usage
Use the AVERAGEIF
formula with the syntax shown below, it has 2 required parameters and 1 optional parameter:
- criteria_range (required):
The range of cells to evaluate against the criterion. - criterion (required):
The string or value to compare against. - 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 theAVERAGEIF
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.