AVERAGEIFS Function in Excel Tutorial

Excel Tutorial

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.

ArgumentMeaning
average_rangeThe cells containing numbers that Excel will average.
criteria_range1The first range Excel will check.
criteria1The first condition that must be satisfied.
criteria_range2The second range Excel will check.
criteria2The 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:

EmployeeRegionProductSales
AmitNorthLaptop40000
NehaSouthLaptop30000
RahulNorthMobile20000
PriyaNorthLaptop50000
KaranSouthMobile25000

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:

EmployeeRegion = NorthProduct = LaptopIncluded?
AmitTRUETRUEYes
NehaFALSETRUENo
RahulTRUEFALSENo
PriyaTRUETRUEYes
KaranFALSEFALSENo

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.

OperatorMeaningExample
>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.

WildcardMeaningExample
*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

EmployeeRegionSales
AmitNorth40000
NehaSouth30000
RahulNorth50000
PriyaEast25000
KaranNorth60000

Excel Formula

=AVERAGEIFS(C2:C6,B2:B6,"North")

Expected Result

50000

Explanation

  • C2:C6 contains the sales values to average.
  • B2:B6 contains 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

EmployeeDepartmentStatusSalary
AmitHRActive30000
NehaITActive45000
RahulHRInactive25000
PriyaHRActive40000
KaranFinanceActive35000

Excel Formula

=AVERAGEIFS(D2:D6,B2:B6,"HR",C2:C6,"Active")

Expected Result

35000

Explanation

Excel checks two conditions:

  1. Department must be HR.
  2. 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

EmployeeRegionProductSales
AmitNorthLaptop40000
NehaSouthLaptop30000
RahulNorthMobile20000
PriyaNorthLaptop50000
KaranSouthMobile25000
SimranNorthLaptop60000

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

EmployeeDepartmentSalary
AmitIT35000
NehaIT45000
RahulHR50000
PriyaIT55000
KaranIT40000
SimranIT60000

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

StudentMarks
Amit55
Neha65
Rahul75
Priya85
Karan80
Simran60

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

EmployeeSales DateSales
Amit05-Oct-202620000
Neha10-Oct-202630000
Rahul15-Sep-202625000
Priya20-Oct-202640000
Karan05-Nov-202635000
Simran28-Oct-202650000

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

EmployeeRegionSales
AmitNorth30000
NehaNorth45000
RahulSouth50000
PriyaNorth60000
KaranNorth35000
SimranSouth40000

Enter these conditions:

CellValue
F2North
G240000

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

EmployeeDepartmentStatusSalary
AmitITActive45000
NehaHRActive30000
RahulITInactive35000
PriyaITActive55000
KaranFinanceActive40000
SimranHRActive35000
MohitITActive60000
PoojaFinanceInactive30000

Your Challenge

Calculate the following results:

  1. Average salary of IT employees.
  2. Average salary of active IT employees.
  3. Average salary of HR employees.
  4. Average salary of active employees earning more than ₹30,000.
  5. 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.