The AVERAGEIFS function in Excel is used to calculate the average of a range of values that meet multiple criteria. Unlike AVERAGEIF, which only uses one criterion, AVERAGEIFS allows for more complex conditions and is ideal for cases where you need to filter data based on several requirements.
Syntax
| Function | Description |
|---|---|
| AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) | Returns the average of cells in a range that meets multiple criteria. |
Parameters
- average_range: The range of cells to average.
- criteria_range1, criteria_range2, ...: The ranges where the criteria are applied.
- criteria1, criteria2, ...: The conditions that define which cells will be averaged.
Example
Let’s consider a scenario where we have a sales report for employees across multiple departments, and we want to calculate the average sales for those working in a specific department during a certain month.
| Employee | Department | Sales | Month |
|---|---|---|---|
| John | Sales | 15000 | Jan |
| Sara | Marketing | 18000 | Feb |
| Mike | Sales | 17000 | Jan |
| Anna | Sales | 16000 | Feb |
| Sam | Marketing | 19000 | Jan |
Task: Find the average sales for employees in the "Sales" department during the month of "Jan."
To solve this, use the AVERAGEIFS function with two criteria: the department is "Sales" and the month is "Jan."
Solution for Example
To find the average sales for employees in the "Sales" department during the month of "Jan":
=AVERAGEIFS(C2:C6, B2:B6, "Sales", D2:D6, "Jan")This formula checks for "Sales" in the Department column (B2:B6) and "Jan" in the Month column (D2:D6) and then averages the corresponding values in the Sales column (C2:C6). The result is the average sales, which is 16000.
Practice Exercise 1
Task: Using the table below, find the average price of products that have a rating of 4 or higher and are in the "Electronics" category.
Hint: Use the AVERAGEIFS function to average the prices where the category is "Electronics" and the rating is 4 or higher.
Practice Exercise 2
Task: Using the table below, find the average order value for customers from the USA who placed more than 10 orders.
Hint: Use the AVERAGEIFS function to average the order values where the country is "USA" and the number of orders placed is greater than 10.
Solution for Practice Exercise 1
To find the average price of products with a rating of 4 or higher in the "Electronics" category:
=AVERAGEIFS(D2:D6, B2:B6, "Electronics", C2:C6, ">=4")This formula checks for "Electronics" in the Category column (B2:B6) and a rating of 4 or higher in the Rating column (C2:C6) and averages the prices in the Price column (D2:D6). The result is the average price, which is 616.67.
Solution for Practice Exercise 2
To find the average order value for USA customers who placed more than 10 orders:
=AVERAGEIFS(D2:D6, B2:B6, "USA", C2:C6, ">10")This formula checks for "USA" in the Country column (B2:B6) and more than 10 orders in the Orders Placed column (C2:C6), then averages the values in the Order Value column (D2:D6). The result is the average order value, which is 1150.