The Excel AVERAGEIFS function, explained interactively.
AVERAGEIFS takes the mean of values that pass every one of multiple conditions. Same multi-criteria pattern as SUMIFS — aggregate range first, then range/criteria pairs — with one gotcha: when no rows match, AVERAGEIFS returns #DIV/0! instead of zero.
How to use AVERAGEIFS
- Type
=AVERAGEIFS(and select the average_range — the column of numbers to average. Comes first, same order as SUMIFS. - Add the first criteria_range (the column to test) followed by the criteria (the value it must match).
- Repeat for each additional condition. AVERAGEIFS applies AND logic — only rows that satisfy every pair contribute to the mean.
- Close the parenthesis. Expect
#DIV/0!when no rows match every criterion — that’s normal, not a bug.
Try the demo below — change Region to “West” or Product to “Gizmo” to see AVERAGEIFS return #DIV/0!.
#DIV/0!, not zero."North".| A | B | C | |
|---|---|---|---|
| 1 | Region | Product | Amount |
| 2 | North | Widget | 120 |
| 3 | North | Widget | 98 |
| 4 | South | Widget | 140 |
| 5 | North | Gadget | 80 |
| 6 | South | Gadget | 90 |
| 7 | South | Widget | 160 |
| 8 | East | Widget | 110 |
| 9 | North | Gadget | 70 |
| 10 | |||
| 11 | AVERAGEIFS result | 109.00 | |
AVERAGEIFS syntax and arguments
One average_range followed by up to 127 pairs of criteria_range and criteria. See Microsoft’s official AVERAGEIFS reference for the canonical specification.
average_range and every subsequent criteria_range, or AVERAGEIFS returns #VALUE!.criteria_range1 must match. Accepts exact values ("North"), comparisons (">=100"), wildcards ("*Widget"), and cell references. Operators must sit inside the quotes.criteria_range + criteria. Every pair is AND’d with every other.criteria1. Pairs can test entirely different columns.AVERAGEIFS examples
Four patterns covering the overwhelming majority of real AVERAGEIFS use.
Example 1: AVERAGEIFS with two exact matches
The canonical case — mean amount for a specific region and product combination.
Takes the mean of C values for rows where column A is North AND column B is Widget. Both conditions must pass.
Example 2: AVERAGEIFS with a numeric threshold
Average only rows above a cut-off — e.g. mean order size for premium customers.
Here C2:C100 appears twice — once as average_range, once as a criteria_range. Perfectly legal. Keep the operator inside the quotes.
Example 3: AVERAGEIFS wrapped in IFERROR
For dashboards, #DIV/0! is noisy. Wrap in IFERROR or IFNA to show a friendlier fallback.
When the Region/Product combination has no matches, IFERROR replaces #DIV/0! with an em dash. Pair with IFERROR or use IFNA if you only want to catch missing-match errors.
Example 4: AVERAGEIFS with dynamic inputs
Dashboard pattern — let users type Region and threshold; AVERAGEIFS recomputes on every change.
E1 holds a region (used directly); E2 holds a numeric threshold, concatenated onto the operator with &. Same pattern as every other -IFS function.
Common AVERAGEIFS errors and fixes
Four failure modes, each with what to check and how to recover.
AVERAGEIFS returns #DIV/0!
Cause: no rows match every criterion. Unlike SUMIFS, an empty average is mathematically undefined — it is not a bug.
Either loosen a criterion, or wrap in IFERROR(..., "—") to display a readable fallback. For dashboards, prefer IFERROR around any AVERAGEIFS that’s user-filtered.
AVERAGEIFS returns #VALUE!
Cause: average_range and the criteria ranges are different heights.
Align the ranges — every range must span exactly the same number of cells. Full-column references (A:A, C:C) avoid the mismatch entirely.
AVERAGEIFS including zeros you meant to exclude
Cause: zero-valued cells in average_range are included in the mean (they aren’t blank).
Add an explicit exclusion: AVERAGEIFS(C:C, A:A, "North", C:C, "<>0"). Or filter zeros in a helper column before averaging.
Mean drifts after data entry
Cause: an entry was added to average_range that qualifies for every criterion — but its value is legitimate and changes the mean.
This is usually correct behaviour, not a bug. If you want a trimmed mean (ignoring outliers), use TRIMMEAN with filtered data or combine AVERAGEIFS with a percentile check in a helper column.
AVERAGEIFS vs AVERAGEIF, SUMIFS & AVERAGE
The averaging family: pick one condition or many, plain mean or filtered.
| Function | Criteria | Empty match result | Argument order |
|---|---|---|---|
| AVERAGEIFS | One or many (AND) | #DIV/0! | average_range, range1, crit1, … |
| AVERAGEIF | Exactly one | #DIV/0! | range, criteria, [average_range] |
| SUMIFS | One or many (AND) | 0 | sum_range, range1, crit1, … |
| AVERAGE | None | #DIV/0! | number1, number2, … |
AVERAGEIFS and SUMIFS share the same argument order (aggregate range first). AVERAGEIF inherited the old SUMIF order instead — range, criteria, average_range — so conversion between AVERAGEIF and AVERAGEIFS flips arguments, exactly like the SUMIF→SUMIFS flip. If you already know SUMIFS, AVERAGEIFS needs no new mental model.
AVERAGEIFS frequently asked questions
6.01Why does AVERAGEIFS return #DIV/0!?▸
When no rows match every criterion, AVERAGEIFS has nothing to divide by and returns #DIV/0!. Unlike SUMIFS (which returns 0 for an empty match), an empty average is mathematically undefined. Wrap in IFERROR or IFNA to substitute a friendlier value.
6.02What’s the difference between AVERAGEIF and AVERAGEIFS?▸
AVERAGEIF supports exactly one criterion. AVERAGEIFS supports up to 127, all AND’d together. AVERAGEIFS also flips the argument order — average_range comes first, matching SUMIFS.
6.03Does AVERAGEIFS ignore blank cells?▸
Yes. AVERAGEIFS skips cells where average_range is blank, boolean, or text. Zero values are not blank and are included in the mean — if that’s not what you want, add a criterion like "<>0".
6.04Can AVERAGEIFS use comparison operators or wildcards?▸
Yes. Keep the operator inside the quotes: "<100", ">=50", "<>Food". Wildcards * and ? also work on text ranges. To inject a cell reference with an operator, concatenate: ">"&B1.
6.05Is AVERAGEIFS AND or OR logic?▸
AND — every criterion must pass for a row to be averaged. For an OR-style weighted average, you’ll need SUMPRODUCT with boolean arrays, or compute two AVERAGEIFS and combine by weights.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.