AVERAGEIFSformula 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).
- How to use
- Examples of using
AVERAGEIFSformula not working?
- Similar formulas to
AVERAGEIFS formula with the syntax shown below, it has 3 required parameters and 2 optional parameters:
- average_range (required):
The range of cells to calculate the average of.
- criteria_range1 (required):
The range of cells to apply the first criterion to.
- criterion1 (required):
The criterion to apply to the first criteria range.
- criteria_range2 (optional):
An additional range of cells to apply a second criterion to.
- criterion2 (optional):
The criterion to apply to the second criteria range.
ExamplesHere are a few example use cases that explain how to use the
AVERAGEIFSformula 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.
AVERAGEIFSnot working? Here are some common mistakes people make when using the
AVERAGEIFSGoogle 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.
The following functions are similar to
AVERAGEIFS 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.
SUMIFSformula 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.
COUNTIFSfunction 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.
You can learn more about the
AVERAGEIFS Google Sheets function on Google Support.