AVERAGEIF & AVERAGEIFS in Excel

Learn how to use AVERAGEIF and AVERAGEIFS in Excel to calculate conditional averages with real-life examples and simple explanations.

Sometimes, calculating the average of all values is not enough—you want the average of values that meet specific conditions. That’s where Excel’s AVERAGEIF and AVERAGEIFS Functions come in.

These powerful tools let you filter your data based on one or more criteria before calculating the average. In this guide, we’ll explain both functions in an easy-to-understand way, with real-life examples and formulas that you can start using right away.

---

1. AVERAGEIF Function in Excel

Syntax:

=AVERAGEIF(range, criteria, [average_range])

• range: The range of cells to apply the condition.
• criteria: The condition to check.
• average_range: (Optional) The actual range of numbers to average.

Example 1: Average Sales of a Specific Region

Region	Sales
North	5000
South	7000
North	8000
East	6000

Formula:

=AVERAGEIF(A2:A5, “North”, B2:B5)

Result: 6500

Real-Life Use Case:

Find the average salary, score, or sales for a single condition like department, product category, or region.

Also Read: Excel AVERAGE Function

---

2. AVERAGEIFS Function in Excel

Syntax:

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

• average_range: The range of numbers to average.
• criteria_range1, criteria1: The first range and its condition.
• You can add multiple criteria ranges.

Example 2: Average Sales for “North” Region in Q1

Region	Quarter	Sales
North	Q1	5000
South	Q1	7000
North	Q2	8000
North	Q1	6000

Formula:

=AVERAGEIFS(C2:C5, A2:A5, “North”, B2:B5, “Q1”)

Result: 5500

Real-Life Use Case:

Calculate the average of:

• Sales in a region during a specific quarter
• Marks of students by subject and gender
• Attendance filtered by team and month

---

3. Key Differences Between AVERAGEIF and AVERAGEIFS

Feature	AVERAGEIF	AVERAGEIFS
Number of Criteria	One	Multiple
Introduced In	Excel 2007	Excel 2007
Use Case	Simple filtering	Complex filtering

---

4. Additional Real-Life Examples

Example 3: AVERAGEIF – Average Score for Passed Students

Student	Score
Ravi	78
Neha	45
Karan	82
Simran	38

=AVERAGEIF(B2:B5, “>=50”)

Result: 80

Example 4: AVERAGEIFS – Average Salary for IT Team in Delhi

Department	Location	Salary
IT	Delhi	55000
HR	Delhi	40000
IT	Mumbai	60000
IT	Delhi	65000

=AVERAGEIFS(C2:C5, A2:A5, “IT”, B2:B5, “Delhi”)

Result: 60000

---

5. Common Errors and Tips

• Use correct cell ranges of equal size.
• Enclose text criteria in double quotes (e.g., "North").
• Use comparison operators like ">10000" in quotes.
• If no value matches the condition, result is #DIV/0! error.

---

Summary

Excel’s AVERAGEIF and AVERAGEIFS Functions help you analyze data more intelligently by applying conditions to your averages. Whether you’re working in HR, Sales, Finance, or Academics, these functions can simplify your reports and help you make better data-driven decisions.

---

FAQs

---

What’s Next?

In the next post, we’ll learn about the Index and Match in Excel