The AVERAGEIFS Function in Excel is an advanced tool that calculates the average (arithmetic mean) of cells based on multiple criteria. Unlike the simpler AVERAGEIF, the AVERAGEIFS function allows you to apply more than one condition, making it ideal for complex data analysis.
At The Coding College, we aim to help you master Excel for real-world applications.
Syntax of the AVERAGEIFS Function
The syntax for the AVERAGEIFS Function is:
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) - average_range: (Required) The range of cells to calculate the average.
- criteria_range1, criteria_range2, …: (Required) The ranges to evaluate against criteria.
- criteria1, criteria2, …: (Required) The conditions to apply to corresponding ranges.
How Does the AVERAGEIFS Function Work?
The function evaluates each criteria_range against its corresponding criteria and calculates the average of cells in average_range that meet all conditions.
Practical Example 1: Sales by Region and Product
Suppose you want to calculate the average sales for “East” region products labeled as “Electronics.”
| Region | Product | Sales (₹) |
|---|---|---|
| East | Electronics | 20000 |
| West | Electronics | 15000 |
| East | Furniture | 25000 |
Formula:
=AVERAGEIFS(C2:C4, A2:A4, "East", B2:B4, "Electronics") 
Practical Example 2: Average Grades Based on Gender and Subject
You want to calculate the average grades for female students in Math.
| Gender | Subject | Grade |
|---|---|---|
| Female | Math | 85 |
| Male | Math | 78 |
| Female | Science | 92 |
Formula:
=AVERAGEIFS(C2:C4, A2:A4, "Female", B2:B4, "Math") 
Using Logical Operators in AVERAGEIFS
Logical operators like >, <, >=, <=, =, and <> can be used in the criteria.
Example: Exclude Low Scores
| Student | Score |
|---|---|
| A | 40 |
| B | 85 |
| C | 90 |
Formula:
=AVERAGEIFS(B2:B4, B2:B4, ">=50") 
Using Wildcards in AVERAGEIFS
The criteria parameter supports wildcards:
- ?: Represents a single character.
- *: Represents multiple characters.
Example: Match Names
| Name | Sales (₹) |
|---|---|
| John | 10000 |
| Johnny | 15000 |
| Joanna | 12000 |
Formula:
=AVERAGEIFS(B2:B4, A2:A4, "Jo*") 
Common Errors in AVERAGEIFS
- #DIV/0!: Returned when no cells meet the criteria.
- #VALUE!: Occurs if criteria ranges and average ranges have different sizes.
Benefits of Using AVERAGEIFS
- Multi-Criteria Analysis: Perfect for analyzing data with complex conditions.
- Dynamic Updates: Automatically updates results with data changes.
- Flexibility: Works with numbers, text, dates, and wildcards.
Explore More with Excel
The AVERAGEIFS Function is an indispensable tool for advanced data analysis in Excel. Combine it with functions like SUMIFS or COUNTIFS for even deeper insights.