Statistical · multi-condition average · Nov 2026

The AVERAGEIFS function,
measured & explained.

Syntax, a live playground, a measured conditional-average gallery, the #DIV/0! trap, and a sourced error library: the multi-condition AVERAGEIFS function.

D1
fx
=AVERAGEIFS(C2:C8,B2:B8,"East")
0
measured gallery rows
0
sourced errors fixed
0
platforms checked
// 01 — Anatomy

How AVERAGEIFS works

Averages the cells in average_range that meet every criteria pair. Available since Excel 2007.

=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
average_rangeThe cells to average (required, first).
criteria_range1The range tested against criteria1.
criteria1The condition: a number, text, or ">150" expression.
...Up to 127 more criteria pairs, all AND-ed.
.bool-moat td.res-text{color:var(--cyan);font-weight:700;font-family:var(--mono)}
// 02 — Multi-condition average

What AVERAGEIFS actually computes

AVERAGEIFS averages cells that meet every condition. It is exactly SUMIFS divided by COUNTIFS — and this page's engine evaluates that decomposition live (AVERAGEIFS itself is not in HyperFormula 3.5). Every row was measured live. Click any row to load it.

Conditional-average gallery — live SUMIFS / COUNTIFS

ExpressionResultWhy
=SUMIFS(C2:C8,B2:B8,"East")/COUNTIFS(B2:B8,"East")225Average East sales
=SUMIFS(C2:C8,B2:B8,"West")/COUNTIFS(B2:B8,"West")200Average West sales
=SUMIFS(C2:C8,A2:A8,"Alice")/COUNTIFS(A2:A8,"Alice")116.66666667Average Alice sales
=SUMIFS(C2:C8,B2:B8,"East",C2:C8,">150")/COUNTIFS(B2:B8,"East",C2:C8,">150")325East AND sales>150
=SUMIFS(C2:C8,B2:B8,"East")900The numerator: East total
=COUNTIFS(B2:B8,"East")4The denominator: East count
=IFERROR(SUMIFS(C2:C8,B2:B8,"Nope")/COUNTIFS(B2:B8,"Nope"),"no match")no matchGuarded empty criteria
=SUMIFS(C2:C8,B2:B8,"<>East")/COUNTIFS(B2:B8,"<>East")200Not-East average

The real Excel function and its criteria patterns

ExpressionResultWhy
=AVERAGEIFS(C2:C8,B2:B8,"East")225average_range first, then criteria pairs
=AVERAGEIFS(C2:C8,B2:B8,"East",C2:C8,">150")325East AND sales>150
=AVERAGEIFS(C2:C8,B2:B8,"<>East")200not-equal criteria
=AVERAGEIFS(C2:C8,A2:A8,"Alice",B2:B8,"East")125Alice AND East (100,150)
⚠ AVERAGEIFS returns #DIV/0! when no cells meet all criteria (the count is 0). Guard it: =IFERROR(AVERAGEIFS(C2:C8,B2:B8,"Nope"),"no match"). Every criteria_range must be the same size and shape as average_range.
AVERAGEIFS = SUMIFS / COUNTIFS. The decomposition works in every Excel version and is the stable way to compute a conditional average. Criteria are AND-ed; for OR, add separate AVERAGEIFS weighted by their counts, or use SUMPRODUCT.
// 03 — Try it live

Interactive AVERAGEIFS playground

Edit the table or type any formula — the HyperFormula engine evaluates the SUMIFS/COUNTIFS decomposition instantly. Load a preset to average a region, combine criteria, and guard an empty match.

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 AVERAGEIFS works

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

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

Master conditional statistics

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

  • AVERAGEIF vs AVERAGEIFS — argument order
  • The SUMIFS / COUNTIFS decomposition
  • Multi-criteria AND and not-equal patterns
  • The #DIV/0! empty-criteria trap
Free while in beta
Browse the function library

No account needed. Every page has a live playground.

// 07 — FAQ

Frequently asked