If you’ve ever needed to calculate the average based on multiple conditions in Excel, you’re in the right place. The AVERAGEIFS function is a powerhouse tool that helps you do just that. In this guide, I’ll walk you through exactly how AVERAGEIFS works and provide the best examples to get the most out of it.
What is AVERAGEIFS in Excel?
Excel’s AVERAGEIFS function computes the average of values that meet multiple criteria. Unlike the simpler AVERAGEIF, which allows only one condition, AVERAGEIFS can handle multiple conditions at once.
AVERAGEIFS Syntax
The syntax of AVERAGEIFS is straightforward but requires some attention to correctly define the conditions:
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)- average_range – The range of values to average.
- criteria_range1 – The first range to apply the condition on.
- criteria1 – The condition that must be met for the corresponding range.
- criteria_range2, criteria2 – (Optional) Additional ranges and their respective conditions.
Basic AVERAGEIFS Example
Let’s say we have the following dataset:
| Product | Category | Sales |
|---|---|---|
| Product A | Electronics | 500 |
| Product B | Furniture | 600 |
| Product C | Electronics | 700 |
| Product D | Furniture | 400 |
If I want to calculate the average sales of products in the “Electronics” category, I would use:
=AVERAGEIFS(C2:C5, B2:B5, "Electronics")Since “Product A” and “Product C” belong to “Electronics” with sales of 500 and 700, the function returns:
(500 + 700) / 2 = 600Using AVERAGEIFS with Multiple Conditions
Let’s extend the example by adding another condition. Suppose I only want to average sales for “Electronics” where the sales number is greater than 550. The formula would look like this:
=AVERAGEIFS(C2:C5, B2:B5, "Electronics", C2:C5, ">550")In this case, only “Product C” meets both conditions, so the result is:
700Common AVERAGEIFS Mistakes and How to Avoid Them
While AVERAGEIFS is a powerful function, beginners often run into some pitfalls. Here are the most common mistakes and how to avoid them:
- Inconsistent Range Sizes – All criteria ranges and the average range must be the same size, otherwise, the function will return an error.
- Wrong Data Type – Ensure that your criteria match the data types in the column you’re referencing. For example, if you’re using a numerical condition, ensure the column contains numbers.
- Logical Errors – When using multiple conditions, double-check that all are correctly applied. A misplaced condition could lead to incorrect results.
Advanced AVERAGEIFS Techniques
Once you’re comfortable with basic usage, you might want to explore more advanced techniques:
- AVERAGEIFS with Dates – You can use the function to filter data within a date range. For example:
=AVERAGEIFS(C2:C100, A2:A100, ">=01-Jan-2023", A2:A100, "<=31-Dec-2023")- Using Cell References Instead of Hardcoding Criteria – Instead of typing values directly, reference a cell to make the function dynamic:
=AVERAGEIFS(C2:C100, B2:B100, E1, C2:C100, ">" & F1)This way, if you change the values in E1 or F1, the formula updates automatically.
Final Thoughts
The AVERAGEIFS function is one of Excel’s best tools for analyzing data based on multiple criteria. Whether you’re working with sales reports, project metrics, or KPI tracking, mastering AVERAGEIFS can save you a lot of time. By keeping in mind the best practices and using dynamic references, you can make your calculations more flexible and accurate.
Other interesting article:
How AVERAGEIF works in Excel? Best AVERAGEIF examples