AVERAGEIFS is an Excel function that calculates the average of a range of numbers based on one or more criteria. It helps you find the average of values that meet specific conditions.
Syntax
AVERAGEIFS(range, criteria_range1, criteria1, [criteria_range2], [criteria2], …)
Arguments
| range | The range of numbers you want to calculate the average for. |
| criteria_range1 | The first range of cells that contains the criteria for averaging. |
| criteria1 | The first criteria that defines which values to include in the average. |
| [criteria_range2] | (Optional) The second range of cells for additional criteria. |
| [criteria2] | (Optional) The second criteria for further refining the average calculation. |
| … | Additional pairs of criteria_range and criteria can be added as needed. |
How to use
You can use the AVERAGEIFS function to find the average of a range of values that meet specific conditions. Here’s how to use it with multiple examples:
Example 1: Calculate the average of scores for students who scored above 80 in the “Math” subject.
|
1 |
=AVERAGEIFS(Scores, Subjects, "Math", Scores, ">80") |
Example 2: Calculate the average of sales for products in the “Electronics” category with a price greater than $500.
|
1 |
=AVERAGEIFS(Sales, Category, "Electronics", Price, ">500") |
Example 3: Calculate the average age of employees in the “Sales” department who have been with the company for more than 5 years.
|
1 |
=AVERAGEIFS(Age, Department, "Sales", Tenure, ">5") |
Example 4: Calculate the average of temperatures in a specific date range.
|
1 |
=AVERAGEIFS(Temperature, Date, ">=2023-01-01", Date, "<=2023-01-31") |
Example 5: Calculate the average of values based on multiple criteria.
|
1 |
=AVERAGEIFS(Values, Criteria1, "A", Criteria2, "B", Criteria3, "C") |
These are just a few examples of how you can use AVERAGEIFS to calculate averages based on specific conditions. You can include multiple criteria pairs to further refine your calculations.