AVERAGEIF Function

Averages the cells that meet one condition.

Category

Statistical

Available in

Excel 2007+, Microsoft 365

Returns

A number

Taught hands-on in Level 3: Intermediate Lookups & Data Summaries · Conditional Summaries module

Overview

AVERAGEIF checks each cell in a range against a condition and averages the matching values — from the same range, or from a parallel range.

Criteria can be a value ("North", 50000), a comparison in quotes (">=50000", "<>North"), a comparison joined to a cell (">="&F2), or a pattern with wildcards — * for any characters, ? for one character ("A*", "?d"). Text matching ignores upper/lower case.

Syntax

=AVERAGEIF(range, criteria, [average_range])

Arguments

ArgumentRequired?What it does
rangeYesThe cells to test.
criteriaYesThe condition, such as "North" or ">50000".
average_rangeNoThe cells to average, the same size as range. Leave it out to average range itself.

Examples

Example 1 — Average by region and above a threshold

The first two test the Region column; the third tests the sales themselves.

E2fx=AVERAGEIF(B2:B8,"North",C2:C8)
ABCDE
1RepRegionSalesMeasureResult
2AnaNorth62000North55800
3BenSouth48500East59875
4CyNorth50000Over 50k62883.33
5DiEast71250
6EdSouth39800
7FayNorth55400
8GusEast48500
=AVERAGEIF(B2:B8,"North",C2:C8) Explain

Common mistakes

  • #DIV/0! No cells matched the condition, so there's nothing to average.
  • Comparison without quotes. =COUNTIF(C2:C8,>50000) is an error. Write ">50000" in quotes, or ">"&F2 to use a cell.
  • Putting the cell inside the quotes. ">F2" compares with the text "F2". Join the cell instead: ">"&F2.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.