Introduction
The AVERAGEIFS function in Excel helps you calculate an average when multiple conditions must be satisfied. For example, you can calculate average sales for a particular region and product, average salary for a specific department and employment status, or average marks for students who meet multiple requirements.
In this tutorial, you will learn how AVERAGEIFS works, understand each argument, and practise it with real-world examples.
1. What Is the AVERAGEIFS Function in Excel?
The AVERAGEIFS function calculates the average of numbers only when all specified conditions are satisfied.
Imagine a company has sales records for different regions and products. You want to calculate the average sales of Laptop products in the North region.
A normal average would include every sales record. But AVERAGEIFS checks both conditions:
- Region must be North.
- Product must be Laptop.
Only the records matching both conditions are included in the average.
Remember: AVERAGEIFS uses AND logic. This means every condition must be TRUE for a record to be included.
2. AVERAGEIFS Function Syntax
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Let’s understand each argument.
| Argument | Meaning |
|---|---|
| average_range | The cells containing numbers that Excel will average. |
| criteria_range1 | The first range Excel will check. |
| criteria1 | The first condition that must be satisfied. |
| criteria_range2 | The second range Excel will check. |
| criteria2 | The second condition that must be satisfied. |
You can add more criteria range and criteria pairs when you need to check additional conditions.
Important: The average range and all criteria ranges should have the same number of rows and columns.
3. How Does AVERAGEIFS Evaluate Multiple Conditions?
Suppose you have the following sales records:
| Employee | Region | Product | Sales |
|---|---|---|---|
| Amit | North | Laptop | 40000 |
| Neha | South | Laptop | 30000 |
| Rahul | North | Mobile | 20000 |
| Priya | North | Laptop | 50000 |
| Karan | South | Mobile | 25000 |
Your requirement is to calculate the average sales for Laptop products in the North region.
The formula is:
=AVERAGEIFS(D2:D6,B2:B6,"North",C2:C6,"Laptop")
Excel checks each record against both conditions:
| Employee | Region = North | Product = Laptop | Included? |
|---|---|---|---|
| Amit | TRUE | TRUE | Yes |
| Neha | FALSE | TRUE | No |
| Rahul | TRUE | FALSE | No |
| Priya | TRUE | TRUE | Yes |
| Karan | FALSE | FALSE | No |
Only Amit’s and Priya’s sales are included.
Calculation:
(40000 + 50000) / 2 = 45000
Expected Result: 45000
This is how AVERAGEIFS works: it checks all conditions and calculates the average using only the records that satisfy every condition.
4. Understanding Criteria in AVERAGEIFS
A criterion is a condition that Excel uses to decide which records should be included.
You can use text, numbers, comparison operators, cell references, and wildcards.
Text Criteria
Use text when you want to match a particular value.
=AVERAGEIFS(D2:D10,B2:B10,"North")
Excel includes records where the region is North.
Comparison Operators
Comparison operators allow you to check numerical conditions.
| Operator | Meaning | Example |
|---|---|---|
> | Greater than | ">5000" |
< | Less than | "<5000" |
>= | Greater than or equal to | ">=5000" |
<= | Less than or equal to | "<=5000" |
<> | Not equal to | "<>HR" |
= | Equal to | "=North" |
For example:
=AVERAGEIFS(D2:D10,D2:D10,">5000")
This calculates the average of values greater than 5000.
Cell Reference Criteria
You can also store a condition in another cell.
Suppose cell F2 contains North.
=AVERAGEIFS(D2:D10,B2:B10,F2)
Excel uses the value in F2 as the condition.
For comparison operators with cell references, join the operator and cell reference using &.
=AVERAGEIFS(D2:D10,D2:D10,">"&F2)
If F2 contains 5000, Excel checks for values greater than 5000.
Wildcard Criteria
Wildcards help you match text patterns.
| Wildcard | Meaning | Example |
|---|---|---|
* | Any number of characters | "Lap*" |
? | Any single character | `”A?” |
~ | Treats a wildcard as a normal character | "~*" |
For example, "Lap*" matches text beginning with Lap, such as Laptop.
Practical AVERAGEIFS Examples in Excel
Let’s practise AVERAGEIFS using company, sales, student, and employee data.
Question 1: Calculate Region-Wise Average Sales
Problem Statement
A company wants to calculate the average sales generated by employees in the North region.
Data
| Employee | Region | Sales |
|---|---|---|
| Amit | North | 40000 |
| Neha | South | 30000 |
| Rahul | North | 50000 |
| Priya | East | 25000 |
| Karan | North | 60000 |
Excel Formula
=AVERAGEIFS(C2:C6,B2:B6,"North")
Expected Result
50000
Explanation
C2:C6contains the sales values to average.B2:B6contains the regions."North"is the condition.
Excel includes 40000, 50000, and 60000.
Average = (40000 + 50000 + 60000) / 3 = 50000.
Concept Covered: Calculating an average using one condition.
Question 2: Calculate Average Salary for an HR Department with Active Employees
Problem Statement
The HR manager wants to calculate the average salary of employees who belong to the HR department and have Active employment status.
Data
| Employee | Department | Status | Salary |
|---|---|---|---|
| Amit | HR | Active | 30000 |
| Neha | IT | Active | 45000 |
| Rahul | HR | Inactive | 25000 |
| Priya | HR | Active | 40000 |
| Karan | Finance | Active | 35000 |
Excel Formula
=AVERAGEIFS(D2:D6,B2:B6,"HR",C2:C6,"Active")
Expected Result
35000
Explanation
Excel checks two conditions:
- Department must be HR.
- Status must be Active.
Only Amit and Priya satisfy both conditions.
Average = (30000 + 40000) / 2 = 35000.
Concept Covered: Using two conditions together with AND logic.
Question 3: Calculate Average Sales for a Particular Region and Product
Problem Statement
A sales manager wants to calculate average sales of Laptop products in the North region.
Data
| Employee | Region | Product | Sales |
|---|---|---|---|
| Amit | North | Laptop | 40000 |
| Neha | South | Laptop | 30000 |
| Rahul | North | Mobile | 20000 |
| Priya | North | Laptop | 50000 |
| Karan | South | Mobile | 25000 |
| Simran | North | Laptop | 60000 |
Excel Formula
=AVERAGEIFS(D2:D7,B2:B7,"North",C2:C7,"Laptop")
Expected Result
50000
Explanation
Excel checks:
- Region = North.
- Product = Laptop.
The matching sales values are 40000, 50000, and 60000.
Average = (40000 + 50000 + 60000) / 3 = 50000.
Concept Covered: Calculating an average using multiple text conditions.
Question 4: Calculate Average Salary Above a Specific Amount
Problem Statement
A company wants to calculate the average salary of employees in the IT department whose salaries are greater than ₹40,000.
Data
| Employee | Department | Salary |
|---|---|---|
| Amit | IT | 35000 |
| Neha | IT | 45000 |
| Rahul | HR | 50000 |
| Priya | IT | 55000 |
| Karan | IT | 40000 |
| Simran | IT | 60000 |
Excel Formula
=AVERAGEIFS(C2:C7,B2:B7,"IT",C2:C7,">40000")
Expected Result
53333.33
Explanation
Excel checks two conditions:
- Department must be IT.
- Salary must be greater than 40000.
The qualifying salaries are 45000, 55000, and 60000.
Average = (45000 + 55000 + 60000) / 3 = 53333.33.
Notice that 40000 is not included because the condition is strictly greater than 40000.
Concept Covered: Combining text conditions with numerical comparison operators.
Question 5: Calculate Average Marks Within a Range
Problem Statement
A school wants to calculate the average marks of students who scored at least 60 but not more than 80.
Data
| Student | Marks |
|---|---|
| Amit | 55 |
| Neha | 65 |
| Rahul | 75 |
| Priya | 85 |
| Karan | 80 |
| Simran | 60 |
Excel Formula
=AVERAGEIFS(B2:B7,B2:B7,">=60",B2:B7,"<=80")
Expected Result
70
Explanation
Excel checks two conditions on the same marks range:
- Marks must be greater than or equal to 60.
- Marks must be less than or equal to 80.
The qualifying marks are 65, 75, 80, and 60.
Average = (65 + 75 + 80 + 60) / 4 = 70.
Both conditions must be TRUE. Therefore, 55 and 85 are excluded.
Concept Covered: Applying multiple numerical conditions to the same range.
Question 6: Calculate Average Sales Between Two Dates
Problem Statement
A company wants to calculate average sales between 1 October 2026 and 31 October 2026.
Data
| Employee | Sales Date | Sales |
|---|---|---|
| Amit | 05-Oct-2026 | 20000 |
| Neha | 10-Oct-2026 | 30000 |
| Rahul | 15-Sep-2026 | 25000 |
| Priya | 20-Oct-2026 | 40000 |
| Karan | 05-Nov-2026 | 35000 |
| Simran | 28-Oct-2026 | 50000 |
Assume the dates in column B are stored as actual Excel dates.
Excel Formula
=AVERAGEIFS(C2:C7,B2:B7,">="&DATE(2026,10,1),B2:B7,"<="&DATE(2026,10,31))
Expected Result
35000
Explanation
Excel checks two date conditions:
- Sales date must be on or after 1 October 2026.
- Sales date must be on or before 31 October 2026.
The qualifying sales values are 20000, 30000, 40000, and 50000.
Average = (20000 + 30000 + 40000 + 50000) / 4 = 35000.
Concept Covered: Using two date conditions to calculate an average within a date range.
Question 7: Calculate Average Sales Using Cell References
Problem Statement
A sales manager wants to calculate average sales based on a selected region and minimum sales amount.
Instead of typing conditions directly into the formula, the manager wants to use cell references.
Data
| Employee | Region | Sales |
|---|---|---|
| Amit | North | 30000 |
| Neha | North | 45000 |
| Rahul | South | 50000 |
| Priya | North | 60000 |
| Karan | North | 35000 |
| Simran | South | 40000 |
Enter these conditions:
| Cell | Value |
|---|---|
| F2 | North |
| G2 | 40000 |
Excel Formula
=AVERAGEIFS(C2:C7,B2:B7,F2,C2:C7,">"&G2)
Expected Result
52500
Explanation
The formula checks:
- Region must match the value in F2 (North).
- Sales must be greater than the value in G2 (40000).
The qualifying sales values are 45000 and 60000.
Average = (45000 + 60000) / 2 = 52500.
The & operator joins the greater-than symbol with the value in G2.
Concept Covered: Making AVERAGEIFS formulas flexible using cell references.
6. Student Practice Tasks
Try these tasks yourself before checking the solutions.
Task 1: Department-Wise Average Salary
Calculate the average salary of employees in the Finance department.
Task 2: Product-Wise Average Sales
Calculate the average sales of Mobile products in the South region.
Task 3: Attendance-Based Average
Calculate the average attendance percentage of employees in the IT department whose attendance is greater than 90%.
Task 4: Marks-Based Average
Calculate the average marks of students who scored at least 50 and less than 75.
Task 5: Date-Based Average Sales
Calculate average sales for transactions between 1 November 2026 and 30 November 2026.
7. Final Student Challenge: Build a Department Performance Report
A company wants to prepare a department performance report using employee data.
Company Data
| Employee | Department | Status | Salary |
|---|---|---|---|
| Amit | IT | Active | 45000 |
| Neha | HR | Active | 30000 |
| Rahul | IT | Inactive | 35000 |
| Priya | IT | Active | 55000 |
| Karan | Finance | Active | 40000 |
| Simran | HR | Active | 35000 |
| Mohit | IT | Active | 60000 |
| Pooja | Finance | Inactive | 30000 |
Your Challenge
Calculate the following results:
- Average salary of IT employees.
- Average salary of active IT employees.
- Average salary of HR employees.
- Average salary of active employees earning more than ₹30,000.
- Average salary of Finance employees whose salary is below ₹40,000.
Goal: Complete the report using AVERAGEIFS formulas only.
8. Key Takeaways
- AVERAGEIFS calculates an average using multiple conditions.
- The first argument is always the average range.
- Every criteria range must have a corresponding criterion.
- Multiple conditions follow AND logic.
- All conditions must be TRUE for a record to be included.
- Comparison operators help filter numerical values and dates.
- Cell references make formulas easier to update.
- All criteria ranges should have the same dimensions as the average range.
- If no records match the conditions, Excel returns
#DIV/0!.
9. Frequently Asked Questions (FAQs)
1. What is the AVERAGEIFS function in Excel?
AVERAGEIFS calculates the average of values that satisfy multiple conditions.
2. What is the difference between AVERAGEIF and AVERAGEIFS?
AVERAGEIF is designed for one condition, whereas AVERAGEIFS can check multiple conditions.
3. Does AVERAGEIFS use AND or OR logic?
AVERAGEIFS uses AND logic. Every specified condition must be satisfied for a record to be included.
4. Can AVERAGEIFS use multiple conditions on the same column?
Yes. You can apply multiple conditions to the same range, such as checking whether marks are greater than or equal to 60 and less than or equal to 80.
5. Can AVERAGEIFS calculate an average between two dates?
Yes. You can use a start-date condition and an end-date condition to calculate the average for a particular date range.
6. Can I use cell references in AVERAGEIFS?
Yes. You can enter criteria in separate cells and use those cell references in your formula.
7. What happens if no records match the conditions?
Excel returns the #DIV/0! error because there are no matching numerical values to average.
8. Can AVERAGEIFS work with text criteria?
Yes. It can check text conditions such as department names, regions, product names, and employee status.
9. Can I use greater-than and less-than conditions in AVERAGEIFS?
Yes. Operators such as >, <, >=, <=, and <> can be used to define numerical or date conditions.
10. Can AVERAGEIFS calculate an average using three or more conditions?
Yes. You can add more criteria range and criteria pairs to check additional conditions.

