The AVERAGEIFS function in Excel calculates the average of cells that meet multiple conditions. It is one of the most useful functions for analyzing filtered data sets, especially in business reports, sales tracking, budgeting, and inventory analysis.
Unlike AVERAGEIF, which allows only one condition, AVERAGEIFS lets you apply multiple criteria at the same time.
In This Guide, You’ll Learn
- What the AVERAGEIFS function does
- The syntax of AVERAGEIFS
- How to average values using multiple conditions
- How to use text and number criteria
- How to use comparison operators like greater than and less than
- Common AVERAGEIFS mistakes and how to fix them
- Real-world uses for AVERAGEIFS
What Does the AVERAGEIFS Function Do?
The AVERAGEIFS function calculates the average of cells that meet multiple specified conditions.
You can use it to answer questions like:
- What is the average sales amount for the East region?
- What is the average order total for approved orders?
- What is the average score for students who passed?
- What is the average inventory value for products in a certain category?
AVERAGEIFS Function Syntax
=AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2)Argument Definitions:
- average_range — The cells containing the numbers you want to average
- criteria_range1 — The first range to evaluate
- criteria1 — The condition for the first range
- criteria_range2 — The second range to evaluate
- criteria2 — The condition for the second range
You can continue adding more criteria ranges and conditions if needed.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> AVERAGEIFS Function Data
Basic AVERAGEIFS Example
Suppose you want to calculate the average sales amount for rows where:
- Region equals East
- Status equals Approved
Use this formula:
Use:
=AVERAGEIFS(C2:C8,A2:A8,"East",B2:B8,"Approved")Excel averages only these rows:
- 1200
- 1500
- 1700
The result is 1466.67
Using Cell References Instead of Typing Criteria
Instead of typing criteria directly into the formula, you can reference cells.
For example:
Use:
=AVERAGEIFS(C2:C8,A2:A8,F1,B2:B8,F2)
This makes your worksheet more flexible because users can change the criteria without editing the formula.
Using Number Conditions with AVERAGEIFS
AVERAGEIFS also works with number-based criteria.
Suppose you want the average sales amount greater than 1000.
Use:
=AVERAGEIFS(C2:C8,A2:A8,F1,B2:B8,F2)Using Multiple Number Conditions
You can combine multiple number conditions.
For example, average sales between 1000 and 1600:
Use:
=AVERAGEIFS(C2:C8,C2:C8,">1000",C2:C8,"<1600")
This averages only sales values:
- Greater than 1000
- Less than 1600
Combining Text and Number Conditions
You can mix text and numeric criteria in the same formula.
Example:
Average East region sales greater than 1000:
Use:
=AVERAGEIFS(C2:C8,A2:A8,"East",C2:C8,">1000")
This filters by:
- Region = East
- Sales > 1000
Common AVERAGEIFS Errors
Ranges Are Different Sizes
All ranges in AVERAGEIFS must be the same size.
This causes errors:
=AVERAGEIFS(C2:C8,A2:A7,"East)The ranges do not match.
Correct version:
=AVERAGEIFS(C2:C8,A2:A8,"East)No Matching Results
If no cells match the criteria, Excel returns:
#DIV/0!This happens because Excel cannot calculate an average from zero matching values.
Text Instead of Numbers in Average Range
AVERAGEIFS ignores text values in the average range.
Make sure the cells being averaged contain actual numbers.
Why AVERAGEIFS Is Useful
AVERAGEIFS is extremely valuable because it lets you calculate averages from filtered subsets of data without manually sorting or filtering your worksheet.
It is commonly used in:
- Sales reporting
- Financial analysis
- Inventory tracking
- Performance dashboards
- Budget analysis
- HR reporting
AVERAGEIFS vs AVERAGEIF
| Function | Conditions Allowed |
|---|---|
| AVERAGEIF | One condition |
| AVERAGEIFS | Multiple conditions |
Use AVERAGEIF for simple calculations and AVERAGEIFS when multiple filters are required.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> AVERAGEIFS Function Data
