AVERAGEIFS in Excel: Syntax, 5 Examples, DIV/0 Fix & IFS Reconciliation | Sheets & Cells
Function · Math & Statistical

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.

Quick answer
AVERAGEIFS returns the arithmetic mean of numbers in a range that meet one or more criteria. Each row is included only if ALL criteria match (AND logic). Structurally identical to SUMIFS with "average" instead of "sum."
Syntax
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])
Working example
=AVERAGEIFS(Amount, Region, "North", Product, "Widget A") → Averages the Amount values where Region = "North" AND Product = "Widget A". With our sample data: $3,124.80 — which equals SUMIFS ($6,249.60) ÷ COUNTIFS (2).
✓
Works in every Excel version since 2007
Excel 2007, 2010, 2013, 2016, 2019, 2021, 365, Excel for the web, Google Sheets, LibreOffice, Apple Numbers. Same generation as SUMIFS and COUNTIFS — universal compatibility.
📗 Free AVERAGEIFS example workbook
9 sheets · same 24-row sales log as SUMIFS/COUNTIFS · includes the Reconciliation Math proof sheet
Download .xlsx (free) Open in Sheets
Category
Math & Statistical
Difficulty
Beginner → Intermediate
Excel version
2007+
Max criteria
127 pairs
127
Max criteria pairs
5
Worked examples
3rd
IFS triad piece
8/10
Templates using it

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.

ArgumentTypeWhat 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.
⚠️ The DIV/0 gotcha — unique to AVERAGEIFS
When zero rows match your criteria, AVERAGEIFS returns #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.
Fragile — breaks on no-match
=AVERAGEIFS(Amount, Region, C2)
Production-ready — graceful fallback
=IFERROR(AVERAGEIFS(Amount, Region, C2), 0)

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.

RegionTransactionsTotalAvg per txn
North8$51,394.15$6,424.27
South6$27,648.10$4,608.02
East5$31,599.75$6,319.95
West5$14,799.65$2,959.93
=AVERAGEIFS(Amount, Region, "North")
→ Returns $6,424.27 — the average North transaction size

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.

=AVERAGEIFS(Amount, Region, "North", Product, "Widget A")
→ Returns $3,124.80 — the average North + Widget A transaction
🔗 The IFS triad reconciles perfectly
On the SUMIFS page, the same filter sums to $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.
=AVERAGEIFS(Amount, Region, "North", Product, "Widget A", Salesperson, "Emma Thompson")
→ Returns $2,499.75 — three criteria, only Emma's row matches

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.

=AVERAGEIFS(Amount, Date, ">="&DATE(2026,1,15), Date, "<="&DATE(2026,2,15))
→ Returns $4,954.78 — average txn size from Jan 15 to Feb 15

Monthly average trend — the story counts hide

MonthTransactionsTotalAvg per txn
Jan 20268$45,594.15$5,699.27
Feb 20268$29,318.35$3,664.79
Mar 20268$52,529.15$6,566.14
The insight only averaging reveals: transaction volume is FLAT (8 per month), but average transaction size dropped 36% from Jan to Feb, then bounced 79% in Mar. If you were only tracking counts (COUNTIFS) or totals (SUMIFS), you'd miss the story entirely — Feb had a temporary shift to smaller deals. That's the whole point of AVERAGEIFS.

Self-updating patterns

=IFERROR(AVERAGEIFS(Amount, Date, ">="&EOMONTH(TODAY(),-1)+1, Date, "<="&EOMONTH(TODAY(),0)), 0)
→ Current-month average, always updating — wrapped in IFERROR for months with no data yet.

04 Wildcards — average across product families

Average across grouped product families without listing every SKU.

=AVERAGEIFS(Amount, Product, "Widget*")
→ Returns $2,382.34 — average Widget-anything transaction (Widget A + Widget B combined)
=AVERAGEIFS(Amount, Product, "Gizmo*")
→ Returns $6,679.81 — Gizmos have nearly 3× higher avg than Widgets
=AVERAGEIFS(Amount, Product, "*Pro*")
→ Returns $10,324.00 — anything with "Pro" in the name (premium tier)
This is where AVERAGEIFS earns its keep: a totals table (SUMIFS by category) would tell you Widgets sold more units, but averages reveal Gizmos and Pro products have 3× and 4× the deal size. Both stories matter — but only averages surface the second one.

* 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, ">5000")
→ Returns $9,312.00 — average of only the transactions above $5K (10 rows)
=AVERAGEIFS(Amount, Amount, "<3000")
→ Returns $1,986.87 — average of only smaller transactions (8 rows)
=AVERAGEIFS(Amount, Amount, ">=2000", Amount, "<=5000")
→ Returns $3,077.51 — mid-tier average (between $2K and $5K)
=AVERAGEIFS(Amount, Region, "<>North")
→ Returns $4,627.97 — average everywhere EXCEPT North
Outlier removal pattern: if your average is being pulled up by a handful of huge transactions, use 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.

Input · Region
North
Output · Avg per transaction
$6,424.27
=IFERROR(AVERAGEIFS(AAmount, ARegion, "North"), "No matches")
Input · Region + Product
North + Widget A
Output · Filtered average
$3,124.80
=IFERROR(AVERAGEIFS(AAmount, ARegion, $C$24, AProduct, $C$25), "No matches")

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:

ResultWhy it happensBroken → 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
📗 Every example above, in one workbook
Same 24-row log as SUMIFS/COUNTIFS · live-filter panels · the Reconciliation Math proof sheet.
Download averageifs-examples-2026.xlsx

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
Why this matters: when your dashboard shows SUMIFS, COUNTIFS, and AVERAGEIFS in the same row (a common pattern), running this reconciliation is your sanity check. If the numbers don't match, one of your three formulas has a subtle bug — usually a different criteria expression, a hardcoded filter that got out of sync, or a mismatched named range.

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:

=AVERAGEIFS(Price, ...) ' → arithmetic mean of prices (wrong for weighted)
=SUMPRODUCT(Price*Qty)/SUM(Qty) ' → weighted average, respecting quantities

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:

PlatformSupports AVERAGEIFS?Notes
Excel 365 (Windows & Mac)✓ YesFull support
Excel 2021✓ YesFull support
Excel 2019 / 2016 / 2013 / 2010 / 2007✓ YesIntroduced in Excel 2007
Excel 2003 and earlier✗ NoUse SUMPRODUCT approach
Excel for the web✓ YesFull support
Excel on iPad & iPhone✓ YesFull support
Google Sheets✓ YesSame syntax, same behavior
LibreOffice Calc✓ YesSame syntax, all versions
Apple Numbers✓ YesSame 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

  1. 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.

  2. Write your first criteria pair

    criteria_range + criteria. Simple text and numbers work as-is; comparisons go in quotes.

  3. Add more criteria as needed

    Each additional filter is another range/criteria pair. All must match — AND logic. Up to 127 pairs.

  4. Wrap the whole thing in IFERROR

    =IFERROR(AVERAGEIFS(...), 0). Non-negotiable for any formula that might return no matches. Use 0, "", or "No data" as the fallback.

  5. 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

🎁 Grab the free AVERAGEIFS workbook
Same 24-row sales log as SUMIFS/COUNTIFS · live-filter panels with IFERROR patterns · the Reconciliation Math proof sheet · DIV/0 error playground.
Download averageifs-examples-2026.xlsx

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 →