Statistical · conditional average · Nov 2026

The AVERAGEIF function,
measured & explained.

Syntax, a live playground, a measured criteria gallery, the #DIV/0! no-match trap, and a sourced error library: conditional averages.

fx
LookupLab Research
By LookupLab Research·Updated Nov 16, 2026·Engine: HyperFormula 3.5·What AVERAGEIF returns
D1
fx
=AVERAGEIF(A2:A5, "Apple", B2:B5)
0
measured gallery rows
0
sourced errors fixed
0
platforms checked
▶ Try the playground Try AVERAGEIF live ↓See the 8 errors
// 01 — Anatomy

How AVERAGEIF works

Averages the cells that meet one criteria. Returns #DIV/0! when nothing matches — guard it with IFERROR.

=AVERAGEIF(range, criteria, [average_range])
rangeThe cells tested against the criteria.
criteriaA number, text, operator or wildcard that selects rows.
average_rangeOptional; the cells actually averaged. Defaults to range.
.bool-moat td.res-text{color:var(--cyan);font-weight:700;font-family:var(--mono)}
// 02 — Conditional average

What AVERAGEIF actually averages

AVERAGEIF averages only the cells that meet one criteria. The surprise is the no-match case: it is an error, not zero. Every row was measured live in HyperFormula. Click any row to load it.

Criteria gallery — no match is #DIV/0!, not 0

ExpressionResultWhy
=AVERAGEIF(A2:A5,"Apple",B2:B5)125(100+150)/2
=AVERAGEIF(B2:B5,">150")2502-arg: average the range itself where >150
=AVERAGEIF(D2:D5,"East",B2:B5)125East amounts averaged
=AVERAGEIF(A2:A5,"Banana",B2:B5)200A single matching row
=AVERAGEIF(A2:A5,"<>Apple",B2:B5)250Not-Apple: (200+300)/2
=AVERAGEIF(A2:A5,"Apple",C2:C5)12.5Average the qty column instead
=AVERAGEIF(B2:B5,"<200")125Below 200: (100+150)/2
=AVERAGEIF(A2:A5,"Missing",B2:B5)#DIV/0!No match: divide by zero

The optional average_range

ExpressionResultWhy
=AVERAGEIF(range, criteria)averages range2-arg: test and average the same range
=AVERAGEIF(range, criteria, average_range)averages average_range3-arg: test one range, average another
⚠ No match returns #DIV/0!. Unlike SUMIF (which returns 0), AVERAGEIF divides by the count of matches — zero matches means divide by zero. Wrap in IFERROR(...,0) or IFERROR(...,"") to show a friendly default.
// 03 — Try it live

Interactive AVERAGEIF playground

Edit the table or type any formula — the engine evaluates instantly. Load a preset to see AVERAGEIF average a cross-column range, use the 2-arg form, and hit the #DIV/0! no-match case.

A1Sample data — click any cell to edit, the formula re-evaluates live
Result
—
✓ readyHyperFormula 3.5
// 04 — Error library

The real-world error library

8 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.

🔍
// 05 — Cross-platform

Where AVERAGEIF works

AVERAGEIF is available across modern Excel, Google Sheets and LibreOffice. It has been in Excel since 2007.

PlatformSupportNotesGotcha
✓ Supported full AVERAGEIF◐ Partial works with caveatsCompiled from public docs · verify against your build
// Go deeper

Master conditional aggregation

AVERAGEIF is one piece. The full course walks the toolkit end to end with live, measured examples.

  • AVERAGEIF / AVERAGEIFS — one vs many conditions
  • The #DIV/0! no-match trap and the IFERROR fix
  • The optional average_range
  • Wildcards and blank-cell handling
Free while in beta
Browse the function library

No account needed. Every page has a live playground.

// 07 — FAQ

Frequently asked