AVERAGEIFS Function

Averages the cells that meet several conditions.

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

AVERAGEIFS averages the values in one range where every condition is true (AND logic). Each condition is a range paired with its criteria.

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

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

Arguments

ArgumentRequired?What it does
average_rangeYesThe cells to average. Note it comes first — unlike AVERAGEIF.
criteria_range1YesThe first range to test, the same size as average_range.
criteria1YesThe condition for criteria_range1.
criteria_range2, criteria2, …NoUp to 126 more range/condition pairs.

Examples

Example 1 — Two conditions at once

Average sales for North reps who sold more than $50,000, and South reps over $40,000.

E2fx=AVERAGEIFS(C2:C8,B2:B8,"North",C2:C8,">50000")
ABCDE
1RepRegionSalesMeasureResult
2AnaNorth62000North >50k58700
3BenSouth48500South >40k48500
4CyNorth50000
5DiEast71250
6EdSouth39800
7FayNorth55400
8GusEast48500
=AVERAGEIFS(C2:C8,B2:B8,"North",C2:C8,">50000") Explain

Cy's 50,000 isn't more than 50,000, so only Ana and Fay are averaged.

Example 2 — A range between two values

Use the same range twice for "between".

=AVERAGEIFS(C2:C8,C2:C8,">=45000",C2:C8,"<60000") Explain

Averages 48,500, 50,000, 55,400 and 48,500 → 50,600.

Common mistakes

  • #DIV/0! No row meets every condition.
  • #VALUE! The ranges are different sizes.
  • 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

  • For OR logic (North or East), add two AVERAGEIFS results weighted by COUNTIFS, or use AVERAGE(FILTER(…)) in Microsoft 365.
  • Related functions: AVERAGEIF, SUMIFS, COUNTIFS, MAXIFS.
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.