For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=FUNCTIONS

AVERAGEIF()

Statistical

Averages the cells in a range that meet a single condition.

AVERAGEIF(range, criteria, [average_range])

AVERAGEIF works like SUMIF, but divides by the count of matches instead of returning the total. It ignores non-matching cells entirely — they don't count toward the average or its denominator.

If no cells match criteria, AVERAGEIF returns #DIV/0!.

AVERAGEIF inherits SUMIF's inconsistent argument order — criteria range first, average range last — so AVERAGEIFS is usually the better choice even for one condition. The failure worth anticipating is an empty result: if no row matches, AVERAGEIF returns #DIV/0! rather than zero or blank, which is correct behaviour but unhelpful in a report. Wrap it in IFERROR with a plain-English fallback, or guard it with COUNTIF, so a category with no activity reads as "No data" rather than an error somebody will paper over.

Arguments

range
The range to test against criteria.
criteria
The condition that decides which cells to include.
average_rangeoptional
The range to average when criteria matches. Defaults to range if omitted.

Examples

=AVERAGEIF(A2:A20, "North", C2:C20)
→3,067

Averages column C only where column A says North.

=AVERAGEIF(C2:C200, "<>0", C2:C200)
→742.10

Excludes zeros from the average — the common case where 0 was entered to mean not recorded rather than a genuine zero.

=AVERAGEIFS(D2:D200, A2:A200, "North", B2:B200, ">="&DATE(2026,1,1))
→689.00

AVERAGEIFS takes the average range first and allows several conditions — the opposite argument order to AVERAGEIF, which catches everyone once.

Notes and gotchas

  • Argument order is AVERAGEIF(range, criteria, average_range) — the tested range first, matching SUMIF rather than AVERAGEIFS.
  • AVERAGEIF(range, "<>0") is the idiomatic way to average while excluding zeros, which is usually what people mean when a mean looks too low.
  • Operator criteria must be text: ">100", or ">"&B1 to compare against a cell.
  • Blanks and text in average_range are skipped, so the denominator may be smaller than the number of matching rows.
  • Returns #DIV/0! rather than 0 when nothing matches — a meaningful distinction, since an average of no values is undefined rather than zero.

Common errors

What goes wrong with AVERAGEIF, and why
You seeBecauseFix
#DIV/0!No cells matched the criteria, or all matching cells are non-numeric.Check with COUNTIF using the same criteria; zero confirms it is a matching problem.
Tips / Tricks / Hacks

AVERAGEIF tricks, tips, and workbook hacks

AVERAGEIF calculates the arithmetic mean of cells that satisfy a single criterion—such as average deal size for one sales rep, average cycle time for completed orders, or average unit cost excluding zero-cost promotional items.

Its single most practical trick is excluding zeroes from an average (=AVERAGEIF(B2:B100, "<>0")) when unpopulated months or placeholder rows contain 0.00 that would otherwise drag the simple AVERAGE down.

Quick AVERAGEIF checks before trusting the result

  • Use =AVERAGEIF(B2:B100, ">0") to average only active months and ignore 0 placeholders.
  • Wrap in IFERROR(..., 0) or IFERROR(..., "No data") because AVERAGEIF returns #DIV/0! whenever zero rows match the criterion.
  • Double-check that range and average_range start on the exact same row—AVERAGEIF shares SUMIF's silent range-resizing quirk!
AVERAGEIF tricks and formula patterns
AVERAGEIF patternAVERAGEIF formulaBest useWatch out
Average while ignoring zero placeholders=AVERAGEIF(B2:B100,"<>0")Monthly forecast templates where future months show $0 until actuals are booked.Omit the 3rd argument when the criteria range and averaged range are the same column.
Average excluding #N/A or errors in a single column=AVERAGEIF(B2:B100,"<9.99E+307")Averaging a calculated lookup column where unmatched rows return #N/A.Any finite number is less than 9.99E+307, while error cells fail the test and are skipped.
Average values above the overall median=AVERAGEIF(B2:B100,">"&MEDIAN(B2:B100))Measuring upper-half performance without extreme bottom-end noise.Concatenate ">" with MEDIAN() using &.
Common AVERAGEIF mistakes and how to fix them
AVERAGEIF mistakeWhat happens in AVERAGEIFFix
Zero matches throwing #DIV/0! into executive dashboardsSelecting a new territory with 0 closed deals turns the Average Deal Size KPI card into #DIV/0!.Wrap with IFERROR(AVERAGEIF(...), 0).
Misaligned average_range start row=AVERAGEIF(A2:A100, "East", C1:C100) averages C1:C99, shifting every matched row by 1.Lock both ranges to identical row boundaries ($A$2:$A$100 and $C$2:$C$100).

AVERAGEIF practice drills

  1. Create a 12-month sales column where months 1–6 have sales and months 7–12 have 0. Compare =AVERAGE(B2:B13) against =AVERAGEIF(B2:B13, ">0").

Related functions