Excel AVERAGEIF & AVERAGEIFS — Conditional Averages and the #DIV/0! No-Match Trap

TL;DR — AVERAGEIFS averages a column, but only the rows that pass every test: =AVERAGEIFS(avg_range, criteria_range1, criteria1, …). It shares the exact criteria grammar of SUMIFS and COUNTIFS, but it has one behaviour that will surprise you: when no row matches, SUMIFS returns 0 — AVERAGEIFS returns #DIV/0!, because you can't average zero numbers. Guard it with IFERROR. Two more things bite: it averages zeros but skips blanks (so "missing = 0" silently drags your mean down), and like SUMIF/SUMIFS the single and plural forms reverse their arguments. Works in Excel 2007+.

=AVERAGEIFS(Score, Class, "A")
=IFERROR(AVERAGEIFS(Score, Class, "A", Score, ">"&G1), "n/a")

AVERAGEIFS is the third face of conditional aggregation: SUMIFS totals, COUNTIFS tallies, AVERAGEIFS means. If you've read the other two, you already know the criteria language — so this guide spends its time on the three things that make averages different and dangerous, not on repeating the syntax.

What you'll learn

• The mental model: the conditional-aggregation engine, now returning a mean
• The headline trap: empty set → #DIV/0!, and the one-wrapper fix
• The silent trap: zeros are averaged, blanks are skipped
• The reversed argument order in AVERAGEIF vs AVERAGEIFS
• Why AVERAGEIFS can't do a weighted average — and what to use instead

The mental model: same engine, returning a mean

AVERAGEIFS keeps the rows where all your tests pass and returns their average — SUMIFS of those rows divided by COUNTIFS of those rows. That definition isn't trivia; it is the source of every trap below. The criteria work exactly as in the sibling functions: operators inside quotes, ">"&G1 to compare against a cell, DATE() for locale-safe date bounds, AND built in across pairs.

' Average score for Class A
=AVERAGEIFS(Score, Class, "A")

' Average score for Class A, above the cutoff in G1 (AND)
=AVERAGEIFS(Score, Class, "A", Score, ">"&G1)

The headline trap: no match means #DIV/0!, not zero

This is the one behaviour that catches everyone, and it follows straight from "sum ÷ count." If no row matches, the count is 0, and dividing by zero is undefined — so AVERAGEIFS returns #DIV/0!. Compare with its siblings on the same empty set:

=SUMIFS(Score, Class, "Z")        ' -> 0        (a sum of nothing is zero)
=COUNTIFS(Class, "Z")             ' -> 0        (a count of nothing is zero)
=AVERAGEIFS(Score, Class, "Z")    ' -> #DIV/0!  (a mean of nothing is undefined)

That asymmetry breaks dashboards: a SUMIFS summary survives an empty category, but the AVERAGEIFS next to it lights up red and cascades into anything referencing it. The fix is to decide what "no data" should display and wrap it:

=IFERROR(AVERAGEIFS(Score, Class, "A"), "n/a")
=IFERROR(AVERAGEIFS(Score, Class, "A"), "")     ' blank, if you prefer

Make IFERROR a reflex on every AVERAGEIFS that feeds a report or a category that might one day be empty. It's not defensive paranoia — it's the difference between "no sales yet" reading as n/a versus a broken sheet.

The silent trap: zeros count, blanks don't

AVERAGEIFS ignores cells in the average range that are blank or text — they don't count toward the numerator or the denominator. But a cell containing 0 is a real number, so it is averaged, and it drags the mean down. This is a data-entry decision disguised as a formula problem:

If a salesperson made no sales and you record that as 0, their zero is averaged in. If you record it as blank, they're excluded entirely. Same "no sales," two completely different averages — and nothing in the formula tells you which one you got.

Decide at the source what "missing" means. If zeros should be excluded from the average, either leave them blank or add a criteria that filters them out: =AVERAGEIFS(Score, Class, "A", Score, "<>0"). The point is to make the choice explicit rather than letting your data-entry convention quietly pick for you.

The reversed argument order (same story as SUMIF)

AVERAGEIF and AVERAGEIFS flip their arguments exactly the way SUMIF and SUMIFS do:

=AVERAGEIF(Class, "A", Score)        ' test range first, average range last
=AVERAGEIFS(Score, Class, "A")        ' average range FIRST, then test pairs

Same trap, same fix: use AVERAGEIFS for everything. The order is consistent with one condition or five, and you sidestep the silent column-swap that comes from copying between the two forms.

The judgment call: AVERAGEIFS gives a simple mean, not a weighted one

This is the conceptual mistake that survives even after you've mastered the syntax. AVERAGEIFS averages the cells in the average range — each matching row counts equally. It cannot give you a weighted average, where each row should count in proportion to a quantity. Average price across orders is not the average customers actually paid; you need price weighted by quantity:

' WRONG question: simple mean of the price cells
=AVERAGEIFS(Price, Region, "West")

' RIGHT for a blended rate: total value ÷ total quantity (weighted)
=SUMIFS(Value, Region, "West") / SUMIFS(Qty, Region, "West")

"Mean of the matching cells" and "blended rate" are different questions, and AVERAGEIFS only answers the first. The same limit applies to a conditional median or mode — there's no MEDIANIFS; reach for FILTER and wrap it: =MEDIAN(FILTER(Score, Class="A")).

How ExcelMaster helps

The dangerous part of a conditional average isn't typing it — it's knowing that an empty category throws #DIV/0!, that recorded zeros skew the mean, and that "average price" usually should be a weighted rate. ExcelMaster reads your intent — average deal size for the West team this quarter, blank if none — and writes the guarded AVERAGEIFS (or the weighted SUMIFS/SUMIFS when that's what you actually meant), so the number answers the question you asked.

Frequently asked questions

Why does AVERAGEIFS return #DIV/0!?

No row matched your criteria, so there's nothing to average and Excel can't divide by a count of zero. Wrap it: =IFERROR(AVERAGEIFS(…), "n/a"). Unlike SUMIFS (which returns 0 on an empty set), AVERAGEIFS always errors when nothing matches.

Does AVERAGEIFS include zeros in the average?

Yes. A cell containing 0 is a real number and is averaged in, lowering the mean. Blank and text cells are skipped entirely. If "missing" data is stored as 0 and shouldn't count, exclude it with a criteria: =AVERAGEIFS(Score, Score, "<>0").

What's the difference between AVERAGEIF and AVERAGEIFS?

AVERAGEIF takes one condition with the test range first; AVERAGEIFS takes one or more with the average range first — the arguments are reversed. Use AVERAGEIFS always to avoid the silent swap.

How do I do a weighted average with conditions?

AVERAGEIFS can't — it weights every row equally. Divide a conditional total by a conditional total: =SUMIFS(Value, Region, "West") / SUMIFS(Qty, Region, "West").

How do I average values between two dates?

Two criteria on the date column, built with DATE(): =AVERAGEIFS(Amount, OrderDate, ">="&DATE(2026,1,1), OrderDate, "<="&DATE(2026,3,31)) — and wrap in IFERROR in case the window is empty.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-06-24.

Related guides: Excel SUMIFS · Excel COUNTIFS · Excel FILTER · Excel UNIQUE