The Averageifs function in Excel is a way of calculating the average of a range of cells based on multiple criteria. It’s an extension of the Averageif function, which only allows for one criterion. Here’s a breakdown of how to use Averageifs:
Syntax
AVERAGEIFS(average_range, criteria_range1, criteria1, …)
- average_range: The cells you want to average.
- criteria_range1: Where to check the first rule.
- criteria1: The first rule.
- …: More ranges and rules if needed. Up to 127 pairs.
How it Works:
AVERAGEIFS checks each cell in criteria_range1 against criteria1. If the condition is met, it then checks the corresponding cell in criteria_range2 against criteria2, and so on for all criteria. Only if all criteria are met for a given row will the corresponding value in average_range be included in the final average calculation.
Examples:
Let’s say you have a dataset with sales data, including columns for Region, Product and Sales Amount.
1. Average Sales of Product in the Region:
To calculate the average sales amount for product A sold in the East region, you would use the following formula:
=AVERAGEIFS(C2:C7, A2:A7, “East”, B2:B7, “A”)
- C2:C7 is the average_range (Sales Amount).
- A2:A7 is the criteria_range1 (Region).
- “East” is the criteria1.
- B2:B7 is the criteria_range2 (Product).
- “A” is the criteria2.
This formula would return and average which is the average of the sales amounts for product in the region.
2. Average Sales in the Region Greater Than:
To calculate the average sales amount in the West region where sales are greater than 100, you would use:
=AVERAGEIFS(C2:C7, A2:A7, “West”, C2:C7, “>100”)
Notice that C2:C7 is used as both the average_range and a criteria_range in this case.
“>100” is the criteria2, using a comparison operator.
3. Using Cell References for Criteria:
Instead of typing the criteria directly into the formula, you can use cell references. This makes your formulas more flexible. For example, if you have “East” in cell E1 and “A” in cell F1, you could use:
=AVERAGEIFS(C2:C7, A2:A7, E1, B2:B7, F1)
Key Points:
Criteria can include:
- Numbers (e.g., 10, 20.5)
- Text (e.g., “East”, “Product A”)
- Comparison operators (e.g., “>100”, “<50”, “<>0”)
- Wildcards:
* * matches any sequence of characters (e.g., “A*” matches “Apple”, “Apricot”)
* ? matches any single character (e.g., “h?t” matches “hat”, “hit”)
Error Handling: If no cells meet all the criteria, AVERAGEIFS returns the #DIV/0! error. You can use IFERROR to handle this. For example: =IFERROR(AVERAGEIFS(C2:C7, A2:A7, “Invalid Region”),0) would return 0 if no matching region is found.
