AVERAGEIFS — Conditional Average with Multiple Criteria
The third piece of Excel's IFS trilogy. SUMIFS answers "how much." COUNTIFS answers "how many." AVERAGEIFS answers "what size each" — the average of the numbers that meet your filters. Same criteria model as its siblings, one important gotcha: the DIV/0 error when nothing matches.
Syntax breakdown
AVERAGEIFS has five arguments to start (or three at minimum), then any number of additional criteria pairs. Same pattern as SUMIFS — with average_range replacing sum_range.
| Argument | Type | What it does |
|---|---|---|
average_range |
REQUIRED | The column of numbers to average. First argument — same slot as SUMIFS's sum_range. Non-numeric cells in this range are ignored (not counted as zero). |
criteria_range1 |
REQUIRED | First column to filter on (Region, Product, Date, etc.). Must be the same size as average_range. |
criteria1 |
REQUIRED | Value or expression to match. Text ("North"), number (100), cell reference (A2), or comparison (">100"). |
criteria_range2 |
OPTIONAL | Second filter column. Add up to 127 range/criteria pairs to layer filters. All must match. |
criteria2 |
OPTIONAL | Second criteria expression. Any row that fails any criteria is excluded from the average. |
#DIV/0! — because you cannot compute the average of no numbers. This is different from SUMIFS and COUNTIFS, which both return 0 for the same empty result. If your criteria might ever return no matches (empty dropdowns, unmapped categories, forward-looking dates), always wrap AVERAGEIFS in IFERROR.Five working examples
Every example uses the same 24-row sales log as the SUMIFS and COUNTIFS workbooks. Q1 2026 data — 4 regions, 5 products, 6 salespeople.
01 Basic AVERAGEIFS — single criterion
Average transaction size by region. Reveals per-transaction economics that totals can't show.
| Region | Transactions | Total | Avg per txn |
|---|---|---|---|
| North | 8 | $51,394.15 | $6,424.27 |
| South | 6 | $27,648.10 | $4,608.02 |
| East | 5 | $31,599.75 | $6,319.95 |
| West | 5 | $14,799.65 | $2,959.93 |
Notice North has both the highest total ($51,394.15) AND a high per-transaction average ($6,424.27). West's average is under half of North's — worth investigating whether West needs bigger deals, or has a different product mix. This is why dashboards show totals AND averages: they answer different questions.
02 Multiple criteria — the canonical $3,124.80 reconciliation
Average sales in North AND for Widget A. Two criteria, AND logic — proves the IFS triad math.
$6,249.60. On the COUNTIFS page, the same filter counts 2 transactions. This page returns $3,124.80 — which is exactly $6,249.60 ÷ 2. The three functions form a mathematical family that always reconciles.Each criteria_range must be the same size as average_range. The order of criteria pairs doesn't affect the result. Add up to 127 criteria pairs.
03 Date range — average transaction size over a period
Average transaction size within a date window. Reveals monthly trends totals can't show.
Monthly average trend — the story counts hide
| Month | Transactions | Total | Avg per txn |
|---|---|---|---|
| Jan 2026 | 8 | $45,594.15 | $5,699.27 |
| Feb 2026 | 8 | $29,318.35 | $3,664.79 |
| Mar 2026 | 8 | $52,529.15 | $6,566.14 |
Self-updating patterns
04 Wildcards — average across product families
Average across grouped product families without listing every SKU.
* matches any characters, ? matches exactly one. Wildcards work only on TEXT criteria_ranges. For numeric ranges, use comparison operators.
05 Comparison operators — average within a range
Average only the transactions above/below thresholds. Rebuild without outliers.
AVERAGEIFS(Amount, Amount, "<"&PERCENTILE(Amount,0.95)) to exclude the top 5%. A common cleanup pattern for reporting metrics.
Interactive playground
Try it Live AVERAGEIFS demonstration
This mirrors the live cells in the workbook. Change the yellow inputs → the blue answer updates. Notice the IFERROR wrap — best practice for anything that might not match.
Download the workbook to test the DIV/0 protection — try filtering to "Nowhere" and watch IFERROR gracefully return "No matches" instead of an ugly error.
Common errors and how to fix them
AVERAGEIFS shares SUMIFS's silent-zero risks but adds one unique failure: #DIV/0! when no rows match. Seven cases:
| Result | Why it happens | Broken → Fix |
|---|---|---|
| #DIV/0! | No rows match the criteria — the classic AVERAGEIFS trap. SUMIFS returns 0 here; AVERAGEIFS errors out. | AVERAGEIFS(Amount, Region, "Nowhere")
IFERROR(AVERAGEIFS(Amount, Region, "Nowhere"), 0) |
| #DIV/0! | All matching rows have empty average_range cells (treated as no data). | AVERAGEIFS(EmptyCol, Region, "North")
IFERROR(AVERAGEIFS(EmptyCol, Region, "North"), 0) |
| #VALUE! | average_range and criteria_range are different sizes (row counts don't match). | AVERAGEIFS(G6:G29, C6:C15, "North")
AVERAGEIFS(G6:G29, C6:C29, "North") |
| Wrong avg | Wrong argument order — argument slots are correct but content mixed up. | AVERAGEIFS(C6:C29, "North", G6:G29)
AVERAGEIFS(G6:G29, C6:C29, "North") |
| #DIV/0! | Missing & concatenation causing zero matches on a comparison. |
AVERAGEIFS(Amount, Amount, ">C2")
IFERROR(AVERAGEIFS(Amount, Amount, ">"&C2), 0) |
| Wrong avg | Trying to compute a WEIGHTED average with AVERAGEIFS (it only does arithmetic mean). | AVERAGEIFS(Price, ...) /* ignores Quantity */
=SUMPRODUCT(Price*Qty) / SUM(Qty) |
| #VALUE! | Reference to a closed workbook (AVERAGEIFS cannot read closed external files). | AVERAGEIFS('[Closed.xlsx]Sheet1'!A:A, ...)
Open the source workbook, or import via Power Query |
The Reconciliation Math — SUMIFS ÷ COUNTIFS = AVERAGEIFS
The unifying identity of the whole IFS family. Total divided by count equals average — always. This table proves it across the full grid using formulas from the free workbook:
The IFS triad, reconciled
Each row: the same filter run through all three functions. Column D (SUMIFS ÷ COUNTIFS) exactly matches column E (AVERAGEIFS) — every time.
| Filter | SUMIFS | ÷ | COUNTIFS | = | AVERAGEIFS |
|---|---|---|---|---|---|
| By region | |||||
| Region = North | $51,394.15 | ÷ | 8 | = | $6,424.27 |
| Region = South | $27,648.10 | ÷ | 6 | = | $4,608.02 |
| Region = East | $31,599.75 | ÷ | 5 | = | $6,319.95 |
| Region = West | $14,799.65 | ÷ | 5 | = | $2,959.93 |
| Multi-criteria — the canonical example | |||||
| North + Widget A | $6,249.60 | ÷ | 2 | = | $3,124.80 |
| North + Gizmo Pro | $24,920.00 | ÷ | 2 | = | $12,460.00 |
| South + Widget A | $4,999.00 | ÷ | 2 | = | $2,499.50 |
| Category = Widgets | $23,823.35 | ÷ | 10 | = | $2,382.34 |
What AVERAGEIFS is NOT — weighted average
AVERAGEIFS computes the arithmetic mean — sum ÷ count. It does NOT do weighted averages. If you have prices AND quantities and want the true average selling price:
A common misuse: computing "average selling price" as AVERAGEIFS of the Price column when transactions have different quantities. The correct answer uses SUMPRODUCT with the weight column.
The IFS family — five functions, one pattern
AVERAGEIFS completes the core IFS trilogy. All five family members share the same criteria model:
Meet the family
Every function below uses the same (aggregate_range, criteria_range1, criteria1, ...) pattern. Only the aggregation verb changes.
SUMIFS, COUNTIFS, and AVERAGEIFS ship with Excel 2007+. MAXIFS and MINIFS require Excel 2019 or 365.
Related functions
Excel version compatibility
AVERAGEIFS ships with every modern Excel version. Same coverage as SUMIFS and COUNTIFS:
| Platform | Supports AVERAGEIFS? | Notes |
|---|---|---|
| Excel 365 (Windows & Mac) | ✓ Yes | Full support |
| Excel 2021 | ✓ Yes | Full support |
| Excel 2019 / 2016 / 2013 / 2010 / 2007 | ✓ Yes | Introduced in Excel 2007 |
| Excel 2003 and earlier | ✗ No | Use SUMPRODUCT approach |
| Excel for the web | ✓ Yes | Full support |
| Excel on iPad & iPhone | ✓ Yes | Full support |
| Google Sheets | ✓ Yes | Same syntax, same behavior |
| LibreOffice Calc | ✓ Yes | Same syntax, all versions |
| Apple Numbers | ✓ Yes | Same syntax |
When to use AVERAGEIFS vs. alternatives
Use AVERAGEIFS when…
- You need an average with one or more filter conditions. The default choice.
- Every observation should count equally. Simple arithmetic mean — good for average transaction size, average rating, average response time.
- You can wrap it in IFERROR. Always safer than a bare AVERAGEIFS in a live dashboard.
Use AVERAGE instead when…
- No filtering needed. Just want the mean of a whole column.
=AVERAGE(A2:A1000).
Use SUMPRODUCT instead when…
- You need a weighted average (each observation contributes based on a weight column like quantity, hours, or size).
- You need OR logic across criteria.
- Your data is in a closed external workbook.
Use MEDIAN instead when…
- Your data has extreme outliers that would pull the mean. Median is more robust for skewed distributions.
How AVERAGEIFS actually works
The algorithm
AVERAGEIFS walks through each row of your criteria_ranges in parallel. For every row, it checks: do ALL criteria pairs match? If YES, add the average_range value to a running sum AND increment a counter. At the end, divide sum by counter. That's the arithmetic mean of the matching rows.
If the counter is zero (no rows matched), Excel returns #DIV/0! — you cannot divide by zero. This is the mathematical reason for the DIV/0 gotcha unique to AVERAGEIFS.
Text criteria — case and whitespace
- Case-insensitive:
"NORTH","north", and"North"all match. - Wildcards:
*for any characters,?for one. - Whitespace is significant — trailing spaces silently break matches. Use
TRIM()to clean.
Numeric criteria — operators inside quotes
- Operators go INSIDE the criteria string:
">100", not>100. - To reference a cell in a comparison, use
&to concatenate:">"&C2.
Empty cells in average_range
Empty and non-numeric cells in average_range are IGNORED — they're not counted as zero. This means AVERAGEIFS matching 10 rows where only 3 have numeric amounts returns the average of those 3, not those 10. If ALL matching rows are empty, you get #DIV/0!.
The 127-criteria limit
AVERAGEIFS accepts up to 127 criteria_range/criteria pairs. If you're approaching double digits, use a PivotTable — much easier to maintain.
Performance notes
AVERAGEIFS is a very fast function in Excel's calc engine. The three drivers of speed:
- Range size. Narrower ranges (
$A$2:$A$5000) beat whole-column references ($A:$A). - Number of criteria pairs. Each additional pair is another comparison per row.
- Data-type consistency. Mixed types in criteria_range force Excel to coerce on every check.
When to switch to a PivotTable
If you're computing dozens of AVERAGEIFS in a matrix (avg by region × product × month), a PivotTable is faster and easier. Right-click a value field → Value Field Settings → Average.
How to write an AVERAGEIFS from scratch
-
Identify the average_range
What column has the numbers you want to average? That's your FIRST argument — same slot as SUMIFS's sum_range.
-
Write your first criteria pair
criteria_range+criteria. Simple text and numbers work as-is; comparisons go in quotes. -
Add more criteria as needed
Each additional filter is another range/criteria pair. All must match — AND logic. Up to 127 pairs.
-
Wrap the whole thing in IFERROR
=IFERROR(AVERAGEIFS(...), 0). Non-negotiable for any formula that might return no matches. Use0,"", or"No data"as the fallback. -
Verify with the reconciliation math
Your AVERAGEIFS result should equal SUMIFS(same filters) ÷ COUNTIFS(same filters). If not, one of the three has a subtle bug. This is the single best sanity check.
Functions used with AVERAGEIFS
Frequently asked questions
What's the difference between AVERAGEIF and AVERAGEIFS?
Three things: (1) AVERAGEIFS takes multiple criteria (up to 127 pairs), AVERAGEIF takes only one. (2) AVERAGEIFS has average_range as the FIRST argument, AVERAGEIF has it LAST — the same gotcha as SUMIF vs SUMIFS. (3) Both have the DIV/0 risk. Use AVERAGEIFS for everything new.
Why is my AVERAGEIFS returning DIV/0?
No rows match your criteria (empty result set), OR all matching rows have empty cells in the average_range. Both cases are mathematically undefined — you can't average zero numbers. Wrap in IFERROR: =IFERROR(AVERAGEIFS(...), 0) gives you a graceful fallback.
How does AVERAGEIFS reconcile with SUMIFS and COUNTIFS?
AVERAGEIFS always equals SUMIFS ÷ COUNTIFS for the same filters. This is the family identity — if your three IFS results don't reconcile, one of them has a subtle criteria bug. The Reconciliation Math section above shows this across 8 different filter combinations.
Can AVERAGEIFS compute a weighted average?
No — AVERAGEIFS only computes the arithmetic mean. For weighted averages (respecting a quantity or weight column), use SUMPRODUCT: =SUMPRODUCT(Price*Qty) / SUM(Qty). Common misuse: treating "average selling price" as an unweighted AVERAGEIFS when transactions have different quantities.
Can AVERAGEIFS handle OR logic?
Not directly — AVERAGEIFS is AND-only. For OR logic, you have to compute weighted-average-style: =(SUMIFS(Amt,Region,"North")+SUMIFS(Amt,Region,"South")) / (COUNTIFS(Region,"North")+COUNTIFS(Region,"South")). Or use SUMPRODUCT for cleaner OR handling.
How do I average within a date range?
Two criteria on the same Date column — one with >= for the start, one with <= for the end: =AVERAGEIFS(Amount, Date, ">="&DATE(2026,1,1), Date, "<="&DATE(2026,3,31)). Same pattern as SUMIFS date ranges.
Are empty cells counted as zero in AVERAGEIFS?
No — empty cells in average_range are IGNORED (not counted as zero). This is different from SUMIFS, which treats them as zero. Practical effect: if 10 rows match but only 3 have numeric amounts, AVERAGEIFS returns the average of those 3, not all 10.
Does AVERAGEIFS work in Google Sheets?
Yes, with identical syntax and behavior. Formulas port cleanly between Excel and Sheets.
Is AVERAGEIFS case-sensitive?
No — AVERAGEIFS is case-insensitive for text comparisons. "NORTH", "north", and "North" all match. For case-sensitive averaging, use SUMPRODUCT with EXACT.
Can AVERAGEIFS pull from a closed workbook?
No — same limitation as SUMIFS and COUNTIFS. AVERAGEIFS returns #VALUE! when its ranges reference a closed external workbook. Open the source workbook, or import the data with Power Query.
What if I want to exclude outliers from an average?
Use comparison operators to cap the range: =AVERAGEIFS(Amount, Amount, "<"&PERCENTILE(Amount, 0.95), Amount, ">"&PERCENTILE(Amount, 0.05)) excludes the top and bottom 5%. Or use MEDIAN, which is robust to outliers by design.
Which templates use AVERAGEIFS?
The same 8 of 10 templates that use SUMIFS and COUNTIFS. Dashboards almost always show all three metrics side by side — total, count, and average — because each answers a different business question. Budget Tracker, Expense Report, Inventory Tracker, Sales Dashboard, KPI Dashboard, Employee Attendance, Timesheet, and Project Timeline all rely on AVERAGEIFS for per-transaction and per-period metrics.
Templates that use AVERAGEIFS
The same 8 templates that use SUMIFS and COUNTIFS also use AVERAGEIFS — dashboards almost always show total, count, AND average side by side:
Skip the syntax. Ask in plain English.
The Sheets & Cells AI Add-in writes AVERAGEIFS, SUMIFS, COUNTIFS — every criteria pair, every IFERROR wrap — right inside Excel. Type "average Q1 sale amount for Widget A in North excluding outliers" and get the working formula, ready to paste.
Try the AI Add-in →