How to Use AVERAGEIF in Excel: Syntax, Example, and Tips

Use the AVERAGEIF function in Excel to find the average of numbers in a range that meet a specific condition. The syntax is AVERAGEIF(range, criteria, [average_range]), where range is checked against the criteria, and average_range is the cells to average if different from range.

Syntax

The AVERAGEIF function has three parts:

• range: The cells you want to test with the condition.
• criteria: The condition that decides which cells to include.
• average_range (optional): The cells to average. If omitted, Excel averages the cells in range.

excel

AVERAGEIF(range, criteria, [average_range])

Example

This example shows how to average scores greater than 70 in a list.

excel

A1: 65
A2: 80
A3: 90
A4: 55
A5: 75

Formula in B1:
=AVERAGEIF(A1:A5, ">70")

Output

81.66666667

Common Pitfalls

Common mistakes include:

• Forgetting to put quotes around criteria with operators, like ">70".
• Using average_range that doesn't match the size of range.
• Expecting AVERAGEIF to count text or blank cells.

excel

Wrong: =AVERAGEIF(A1:A5, ">70")  (missing quotes around ">70")
Right: =AVERAGEIF(A1:A5, ">70")

Quick Reference

Part	Description	Example
range	Cells to check the condition	A1:A10
criteria	Condition to test, in quotes if needed	">50"
average_range	Cells to average if different from range	B1:B10 (optional)

Key Takeaways

Use AVERAGEIF to average only cells that meet a condition.

Always put criteria with operators inside quotes, like ">10".

If average_range is omitted, the function averages the range cells tested.

Make sure average_range size matches range size if used.

AVERAGEIF ignores text and blank cells in the average calculation.