AVERAGEIFfunction 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
- Examples of using
AVERAGEIFformula not working?
- Similar formulas to
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.
ExamplesHere are a few example use cases that explain how to use the
AVERAGEIFformula in Google Sheets.
Calculate the average of all sales over $1000
AVERAGEIF to calculate the average of all sales over $1000 in a sales report.
Calculate the average rating for all movies released in 2020
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
AVERAGEIF to calculate the average score for a student's top 3 test results in a school grading system.
AVERAGEIFnot working? Here are some common mistakes people make when using the
AVERAGEIFGoogle 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.
The following functions are similar to
AVERAGEIF or are often used with it in a formula:
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.
SUMIFformula 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.
COUNTIFformula 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.
You can learn more about the
AVERAGEIF Google Sheets function on Google Support.