AVERAGEIF in Excel: Average by One Condition | 5 Examples + DIV/0 Trap Fix | Sheets & Cells

Function · Statistical

AVERAGEIF — Average by One Condition

The third and youngest member of the Legacy IFS trio. Point AVERAGEIF at a range, give it one condition, get back the mean of the matching values. Same shape as SUMIF — but with a nasty DIV/0 trap when zero rows match that neither SUMIF nor COUNTIF shares. Wrap in IFERROR and you're safe.

✓

Wide support since Excel 2007

Works in Excel 365, 2021, 2019, 2016, 2013, 2010, 2007, Excel for the web, Google Sheets, LibreOffice, and Apple Numbers. Not available in Excel 2003 or earlier — for those, use SUMIF/COUNTIF combined (Example 5). No _xlfn. prefix. Wildcards (* and ?) work the same as SUMIF and COUNTIF.

🎯

The argument-order flip — AVERAGEIF vs AVERAGEIFS

AVERAGEIF and AVERAGEIFS have the same argument-order flip as SUMIF/SUMIFS. Muscle memory writes AVERAGEIF but the person switched to AVERAGEIFS — or vice versa. Different from COUNTIF/COUNTIFS, which use identical first-pair order. Learn once, remember forever.

AVERAGEIF (this page)

AVERAGEIF(range, criteria, [avg_range])

avg_range LAST, and optional. If omitted, AVERAGEIF averages the criteria range itself. Only ONE condition supported.

AVERAGEIFS (multi-condition)

AVERAGEIFS(avg_range, criteria_range1, criteria1, ...)

avg_range FIRST, and required. Then pairs of criteria_range/criteria. Handles unlimited conditions.

⚠️

The DIV/0 trap — the AVERAGEIF-only landmine

When your criteria matches ZERO rows, AVERAGEIF returns a #DIV/0! error — not zero, not blank. This is because "average = sum ÷ count" and when count is zero, you're dividing by zero. Unique to AVERAGEIF in the trio: SUMIF returns 0 on no matches, COUNTIF returns 0, but AVERAGEIF errors out.

SUMIF, 0 matches

→ 0

COUNTIF, 0 matches

→ 0

AVERAGEIF, 0 matches

→ #DIV/0! error

Standard defense: wrap in IFERROR. Full worked example in Example 4 below.

Quick answer

AVERAGEIF returns the arithmetic mean of cells in a range where a condition on a (possibly different) range is met. Three arguments: the range to test, the criteria to test against, and — optionally — a different range whose values get averaged.

Syntax

=AVERAGEIF(range, criteria, [average_range])

Working example

=AVERAGEIF(SalesRep, "Michael Chen", SalesAmount) → Returns $8,300.00 — Michael's three sales averaged. range = SalesRep (where to CHECK the condition) criteria = "Michael Chen" (what to LOOK for) average_range = SalesAmount (what to AVERAGE)

📗 Free SUMIF + COUNTIF + AVERAGEIF workbook

9 sheets · 18-row Q1 2026 sales log · AVERAGEIF basics + advanced (comparison, wildcards, DIV/0 handling) · SUMIF · COUNTIF · legacy-vs-IFS comparison

Download .xlsx (free) Open in Sheets

Syntax breakdown

AVERAGEIF takes up to three arguments — first two required, third optional. Same shape as SUMIF exactly.

Argument	Type	What it does
`range`	REQUIRED	The range where the criteria will be evaluated. Text, numbers, or dates — AVERAGEIF walks each cell against the criteria.
`criteria`	REQUIRED	What to look for. Literal (`"Emma Thompson"`, `5000`), comparison in quotes (`">=5000"`, `"<>North"`), cell reference (`B2`), or wildcard pattern (`"Widget*"`).
`average_range`	OPTIONAL	The range whose values get averaged when criteria match. Must be the same shape as `range`. If omitted, AVERAGEIF averages `range` itself.

The "omitted average_range" trick: when you write =AVERAGEIF(SalesAmount, ">=5000") with no third argument, AVERAGEIF uses the range as both test AND average. Result: the average of all values ≥ $5,000. Compact for numeric-threshold means.

AVERAGEIF ignores blank cells and non-numeric values in the average_range. If a matching row has blank in the average_range, that row is excluded from BOTH the sum and the count — so it doesn't shift the average toward zero. Text values in the average_range are similarly skipped.

Five working examples

Every example uses the 18-row Q1 2026 sales log from the workbook. Named ranges: SalesRep, SalesProduct, SalesRegion, SalesAmount, SalesDate.

01 Exact match — average sale by rep

The bread-and-butter AVERAGEIF use. "What's the average deal size for each rep?"

=AVERAGEIF(SalesRep, "Michael Chen", SalesAmount)

→ Returns $8,300.00 — Michael's three sales averaged (24,900 ÷ 3)

Rep	Sales count	Total	AVERAGEIF result
Emma Thompson	4	$23,700	$5,925.00
David Kim	4	$23,200	$5,800.00
Michael Chen	3	$24,900	$8,300.00
Sofia Rodriguez	3	$12,700	$4,233.33
Aisha Patel	2	$12,300	$6,150.00
James Wilson	2	$11,600	$5,800.00

Reading the answer: Michael Chen ($8,300 avg) closes the biggest deals on average despite fewer records. Aisha Patel ($6,150) has the second-highest per-deal size. Small counts can hide big performance — AVERAGEIF surfaces this instantly.

02 Wildcards — average across a product family

Average deal size for a WHOLE product line without listing each variant separately.

=AVERAGEIF(SalesProduct, "Widget*", SalesAmount)

→ Returns $3,100.00 — average of all Widget A + Widget B sales combined (27,900 ÷ 9)

=AVERAGEIF(SalesProduct, "Gadget*", SalesAmount)

→ Returns $7,800.00 — Gadget X + Gadget Y (46,800 ÷ 6). Gadgets are far more expensive on average.

Reps whose name contains "Kim"

=AVERAGEIF(SalesRep, "*Kim*", SalesAmount)

→ Returns $5,800.00 — David Kim's sales in our data

Wildcards only work in text criteria, not numbers. Same rule as SUMIF and COUNTIF. For numeric pattern matching, convert with TEXT() first — but you almost always want a comparison operator instead.

03 Comparison operators — with average_range OMITTED

Average all sales at or above $5,000. Since the range being tested IS the range being averaged, omit the third argument.

=AVERAGEIF(SalesAmount, ">=5000")

→ Returns $8,944.44 — average of the 9 sales that were $5,000 or more (80,500 ÷ 9)

Below-threshold average

=AVERAGEIF(SalesAmount, "<3000")

→ Returns $2,460.00 — 5 small sales averaged (12,300 ÷ 5)

Exclusion filter

=AVERAGEIF(SalesRep, "<>Emma Thompson", SalesAmount)

→ Returns $6,050.00 — everyone EXCEPT Emma (84,700 ÷ 14)

Comparison operators go INSIDE quotes. Write ">=5000", not >=5000. Same rule as every *IF and *IFS function. Applies to >, <, >=, <=, <>, and =.

04 ⚠️ The DIV/0 trap and its IFERROR fix

The single most important defensive pattern for AVERAGEIF. Zero matches → DIV/0 error → broken dashboards. Wrap in IFERROR and problem solved.

The broken formula

=AVERAGEIF(SalesRep, "Fake Person", SalesAmount)

→ Returns #DIV/0! — no matches, count is 0, division by zero

The IFERROR fix

=IFERROR(AVERAGEIF(SalesRep, "Fake Person", SalesAmount), "No matches")

→ Returns "No matches" — gracefully handled, no error propagation

Alternative fallback values

=IFERROR(AVERAGEIF(SalesRep, B2, SalesAmount), 0)

→ Return 0 if no matches. Useful if downstream formulas expect numbers, not text.

=IFERROR(AVERAGEIF(SalesRep, B2, SalesAmount), "")

→ Return empty string if no matches. Cleaner in cell displays, but breaks downstream math.

Why AVERAGEIF errors but SUMIF and COUNTIF don't

Function on zero matches	Math behind it	Result
SUMIF(...)	Sum of zero items = 0	0 (safe)
COUNTIF(...)	Count of zero items = 0	0 (safe)
AVERAGEIF(...)	0 ÷ 0 (sum ÷ count)	#DIV/0!

Make the IFERROR wrap a habit for AVERAGEIF. In real dashboards, criteria often come from user input, dropdowns, or upstream cells that might contain values with no matching rows in your data. Without IFERROR, one missing category shows a red #DIV/0 across your whole report. With it, your worst case is "No matches" or 0 — informative, not broken.

05 Cell reference criteria — dynamic averaging

Instead of hard-coding "Michael Chen", point the criteria at a cell. Change the cell → the average updates. Classic dashboard pattern.

=AVERAGEIF(SalesRep, B2, SalesAmount)

→ If B2 = "Michael Chen" → $8,300. Change to "Sofia Rodriguez" → $4,233.33. No formula edit needed.

Dynamic threshold

=AVERAGEIF(SalesAmount, ">="&B2)

→ If B2 = 5000, returns average of sales ≥ $5,000. The & concatenates the operator with the cell value at evaluation time.

Combined with IFERROR — production-ready

=IFERROR(AVERAGEIF(SalesRep, B2, SalesAmount), "No matches for " & B2)

→ If the rep in B2 has no sales, returns "No matches for [rep name]". Informative fallback that names the missing entity.

AVERAGEIF on Excel 2003 — the SUMIF/COUNTIF trick

AVERAGEIF was added in Excel 2007. Before that, users combined SUMIF and COUNTIF manually:

=SUMIF(SalesRep, "Michael Chen", SalesAmount) / COUNTIF(SalesRep, "Michael Chen")

→ Returns $8,300.00 — identical to AVERAGEIF(SalesRep, "Michael Chen", SalesAmount)

This SUMIF/COUNTIF pattern still works today. If you ever need to share a workbook with Excel 2003 users (rare but real in some corporate environments), or if you want more control over how "no matches" is handled at the division step, this pattern is your escape hatch.

Interactive playground

Try it Live AVERAGEIF demonstration

Mirrors live cells from the workbook. Edit the yellow input → the blue answer updates.

Input · Rep name

Michael Chen

Output · Average deal

$8,300.00

=AVERAGEIF(SalesRep, B2, SalesAmount)

Input · Rep name

Fake Person

Output · IFERROR-safe

No matches

=IFERROR(AVERAGEIF(SalesRep, B2, SalesAmount), "No matches")

Download the workbook to try AVERAGEIF patterns across the full 18-row sales log.

The Legacy IFS trio — one condition, three metrics

Meet the family

Three sibling functions with the same single-condition shape. Each computes a different statistic on matching rows. This page is AVERAGEIF — the mean.

SUMIF

Total value

SUMIF(rep, "Emma", amt) → $23,700

COUNTIF

How many rows

COUNTIF(rep, "Emma") → 4

AVERAGEIF

Mean value

AVERAGEIF(rep, "Emma", amt) → $5,925

Consistency across the trio: same argument shape, same wildcard rules, same comparison-operator syntax. Only differences: SUMIF/COUNTIF are Excel 1.0+; AVERAGEIF is 2007+. And only AVERAGEIF has the DIV/0 trap on zero matches — the others gracefully return 0.

AVERAGEIF vs AVERAGEIFS — the same question, both ways

Same result. Different argument order.

Every AVERAGEIF query rewrites as AVERAGEIFS — just move average_range from the end to the beginning. Same pattern as SUMIF → SUMIFS. Six worked comparisons:

Question	Legacy — AVERAGEIF	Modern — AVERAGEIFS
Michael Chen's average sale	=AVERAGEIF(SalesRep, "Michael Chen", SalesAmount) → $8,300.00	=AVERAGEIFS(SalesAmount, SalesRep, "Michael Chen") → $8,300.00
Widget A average	=AVERAGEIF(SalesProduct, "Widget A", SalesAmount) → $2,560.00	=AVERAGEIFS(SalesAmount, SalesProduct, "Widget A") → $2,560.00
Average sale in North	=AVERAGEIF(SalesRegion, "North", SalesAmount) → $5,925.00	=AVERAGEIFS(SalesAmount, SalesRegion, "North") → $5,925.00
All "Gadget" products avg	=AVERAGEIF(SalesProduct, "Gadget*", SalesAmount) → $7,800.00	=AVERAGEIFS(SalesAmount, SalesProduct, "Gadget*") → $7,800.00
Average sale ≥ $5,000	=AVERAGEIF(SalesAmount, ">=5000") → $8,944.44	=AVERAGEIFS(SalesAmount, SalesAmount, ">=5000") → $8,944.44
Emma's Widget-only avg (2 conditions)	AVERAGEIF cannot do this — only one condition	=AVERAGEIFS(SalesAmount, SalesRep, "Emma Thompson", SalesProduct, "Widget*") → $3,050.00

Bonus: DIV/0 behavior differs too. Legacy AVERAGEIF returns #DIV/0 on zero matches. AVERAGEIFS ALSO returns #DIV/0 — this trap survives migration. Both need IFERROR wrapping for defensive use.

Common errors and how to fix them

Result	Why it happens	Broken → Fix
DIV/0 error	Zero rows match the criteria. AVERAGEIF's fatal flaw.	`=AVERAGEIF(SalesRep, "Fake", SalesAmount) // no matches → DIV/0` `=IFERROR(AVERAGEIF(SalesRep, "Fake", SalesAmount), "No matches")`
DIV/0 (subtle)	Matches exist but average_range values are all blank or non-numeric.	`Matching rows have blank amount cells → count is 0 for averaging` `Same IFERROR pattern; or clean data with SUMIF/COUNTIF split`
VALUE error	Wildcards used against a numeric range.	`=AVERAGEIF(SalesAmount, "5*", SalesAmount) // wildcards on numbers` `=AVERAGEIF(SalesAmount, ">=5000", SalesAmount)`
Wrong average	range and average_range different shapes.	`=AVERAGEIF(A2:A20, "X", B2:B15) // range mismatch` `=AVERAGEIF(A2:A20, "X", B2:B20) // same size`
Cell ref as text	Comparison operator jammed against cell reference without `&`.	`=AVERAGEIF(SalesAmount, ">=B2") // literal ">=B2"` `=AVERAGEIF(SalesAmount, ">="&B2)`
NAME error	Used on Excel 2003 — AVERAGEIF didn't exist yet.	`=AVERAGEIF(range, crit, avg_range) // fails on Excel 2003` `=SUMIF(range, crit, avg_range) / COUNTIF(range, crit)`

📗 Every example above, in one workbook

Exact match · wildcards · comparison operators · DIV/0 trap · IFERROR fix · Excel 2003 compatibility trick · Legacy-vs-IFS comparison.

Download legacy-ifs-examples-2026.xlsx

Related functions

Complementary functions

Excel version compatibility

AVERAGEIF was added in Excel 2007 — later than SUMIF and COUNTIF, which have been around since Excel 1.0. Nearly universal now, but with one important exception:

Platform	Supports AVERAGEIF?	Notes
Excel 365 (Windows & Mac)	✓ Yes	Full support
Excel 2021 / 2019 / 2016 / 2013 / 2010 / 2007	✓ Yes	Full support
Excel 2003 and earlier	✗ No	Not available — use SUMIF/COUNTIF
Excel for the web	✓ Yes	Full support
Excel on iPad & iPhone	✓ Yes	Full support
Google Sheets	✓ Yes	Same syntax
LibreOffice Calc	✓ Yes	Full support
Apple Numbers	✓ Yes	Full support

Excel 2003 workaround: Use =SUMIF(range, criteria, avg_range) / COUNTIF(range, criteria). Mathematically identical to AVERAGEIF. Bonus: gives you explicit control over the DIV/0 handling — if COUNTIF returns 0, you can catch it with IF before the division: =IF(COUNTIF(range, criteria)=0, "No matches", SUMIF(range, criteria, avg_range)/COUNTIF(range, criteria)).

How to write an AVERAGEIF from scratch

Identify the range you want to TEST
What column holds the values you're checking against a condition? That's the first argument.
Write the criteria
Exact text in quotes: "Emma Thompson". Comparison operators inside quotes: ">=5000". Cell references bare: B2. Cell reference with operator: ">="&B2.
Decide if you need a separate average_range
If the values you're testing ARE the values you want to average (like "average all amounts ≥ 5000"), omit average_range. Otherwise pass it as the third argument.
Wrap in IFERROR — always
AVERAGEIF is the only *IF that errors on zero matches. Make =IFERROR(AVERAGEIF(...), "No matches") your default habit for anything user-facing.
If you need a second condition — switch to AVERAGEIFS
AVERAGEIF is single-condition. For "Emma AND Widget A" or "January to March", migrate to AVERAGEIFS. Remember the argument-order flip: avg_range moves to the FIRST position.

Functions used with AVERAGEIF

AVERAGEIFS SUMIF COUNTIF AVERAGE MEDIAN IFERROR IF SUM COUNT SUMPRODUCT DATE VALUE

🎁 Grab the free Legacy IFS trio workbook

9 sheets covering SUMIF, COUNTIF, AVERAGEIF — basics, wildcards, comparison operators, DIV/0 handling, plus the full legacy-vs-IFS comparison.

Download legacy-ifs-examples-2026.xlsx

Frequently asked questions

What's the difference between AVERAGEIF and AVERAGEIFS?

AVERAGEIF handles ONE condition; AVERAGEIFS handles multiple. Argument order is FLIPPED: AVERAGEIF puts average_range LAST (optional); AVERAGEIFS puts average_range FIRST (required). Same flip as SUMIF/SUMIFS — unlike COUNTIF/COUNTIFS which share the same order.

Why does AVERAGEIF return #DIV/0?

Because no rows matched the criteria, count is zero, and average = sum ÷ count is 0/0 — mathematically undefined. Unique to AVERAGEIF: SUMIF returns 0 on no matches (sum of nothing = 0) and COUNTIF returns 0. Wrap AVERAGEIF in IFERROR to guard: =IFERROR(AVERAGEIF(...), "No matches").

What happens if I omit the average_range?

AVERAGEIF averages the criteria range itself. Useful when the range being tested IS the range being averaged — like "average of all values ≥ 5000". Same trick works for SUMIF; COUNTIF has no equivalent since it doesn't sum anything.

Does AVERAGEIF ignore blank cells?

Yes. Matching rows with blank cells in average_range are excluded from both the sum AND the count. This prevents blanks from artificially pulling the average toward zero. Non-numeric (text) values in average_range are similarly skipped.

Can AVERAGEIF handle multiple conditions?

No. AVERAGEIF is single-condition only. For "rep = Emma AND product = Widget" or "date between X and Y", switch to AVERAGEIFS. The DIV/0 trap applies to both — always wrap either in IFERROR for user-facing formulas.

Do wildcards work in AVERAGEIF?

Yes — * matches any sequence of characters, ? matches exactly one. Only in TEXT criteria, not numeric ranges. Same rule as SUMIF and COUNTIF.

Does AVERAGEIF work in Excel 2003?

No — AVERAGEIF was added in Excel 2007. For 2003 compatibility, use =SUMIF(range, criteria, avg_range) / COUNTIF(range, criteria). Mathematically identical, and gives you explicit control over the divide-by-zero case.

Does AVERAGEIF work in Google Sheets?

Yes, identically. Same syntax across Google Sheets, LibreOffice, and Apple Numbers. DIV/0 behavior is the same too — the trap exists everywhere.

Is AVERAGEIF case-sensitive?

No. AVERAGEIF treats "Emma", "emma", and "EMMA" as the same. For case-sensitive conditional averaging, combine SUMPRODUCT with EXACT: =SUMPRODUCT(EXACT(range,"Emma")*avg_range) / SUMPRODUCT(--EXACT(range,"Emma")).

Why is my AVERAGEIF returning a wrong number?

Most common: numbers stored as text in average_range (fix with VALUE), or range and average_range having different shapes (they must be the same size). Also check for stray whitespace in criteria: "Emma " won't match "Emma".

Should I use AVERAGEIF or AVERAGEIFS?

Reasonable default: AVERAGEIFS, since it handles both single- and multi-condition cases with consistent syntax across the *IFS family. But AVERAGEIF is fine for one-condition cases and reads slightly more naturally. Either way, wrap in IFERROR — the DIV/0 trap survives migration.

Which templates use AVERAGEIF?

Templates showing per-category averages. Prominently: KPI Dashboard (average by metric type), Sales Dashboard (average deal size per rep), Employee Attendance (average hours per employee), Project Timeline (average task duration), Inventory Tracker (average stock levels), Timesheet (average hours per project), and Expense Report (average claim amount by category).

Templates that use AVERAGEIF

7 of our 10 templates use AVERAGEIF for per-category means:

Skip the syntax. Ask in plain English.

The Sheets & Cells AI Add-in writes AVERAGEIF, AVERAGEIFS, and every conditional-average pattern — right inside Excel. Type "average deal size by rep" and get the working formula, ready to paste. IFERROR wrapper included by default.

Try the AI Add-in →