Statistical

AVERAGEIFS Function in Excel

Returns the average of cells that meet multiple criteria.

Syntax

  • =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)

Arguments

  • average_range (required): Cells to average
  • criteria_range1 (required): First range to evaluate
  • criteria1 (required): First criteria

Examples

  • =AVERAGEIFS(D2:D10, B2:B10, "Sales", C2:C10, ">1000") - Average sales over 1000 - Result: Average value

AVERAGEIFS best practices

  • Use AVERAGEIFS when the average depends on multiple criteria.
  • Make criteria ranges consistent with the average range to avoid misleading results.

Excel AVERAGEIFS function documentation

  • AVERAGEIFS calculates the average of cells that meet multiple criteria.
  • Syntax: =AVERAGEIFS(average_range, criteria_range1, criteria1, ...).
  • Use for KPI averages by region, product, period, or rep — more flexible than [AVERAGEIF](/functions/averageif/).
  • Related: [SUMIFS](/functions/sumifs/), [COUNTIFS](/functions/countifs/), [AVERAGEIF](/functions/averageif/).

AVERAGEIFS syntax and criteria rules

  • average_range: numbers to average — top-left cell size drives dimensions.
  • criteria_rangeN must match size/shape of average_range (or be single column/table column).
  • Text criteria in quotes; cell references without extra quotes.
  • Averages only numeric cells in average_range meeting all criteria — text ignored.

Step-by-step: average deal size by region and quarter

  • Step 1 — Table Sales with Region, CloseDate, Amount.
  • Step 2 — Criteria cells: Region in G1, Quarter label in H1.
  • Step 3 — =AVERAGEIFS(Sales[Amount], Sales[Region], G1, Sales[CloseDate], ">="&J1, Sales[CloseDate], "<="&K1).
  • Step 4 — Use DATE bounds for quarter instead of guessing text dates.
  • Step 5 — Compare with pivot average for validation.

AVERAGEIFS vs AVERAGEIF vs pivot

  • AVERAGEIF — one condition only.
  • AVERAGEIFS — multiple AND conditions.
  • Pivot — exploratory; AVERAGEIFS — live dashboard cell.
  • Problem: zero matches returns #DIV/0! — wrap [IFERROR](/functions/iferror/).

Worked examples to copy

  • One criteria: =AVERAGEIFS(C:C, A:A, "East").
  • Two criteria: =AVERAGEIFS(Score, Dept, "Sales", Status, "Closed").
  • Exclude zeros: add criteria range Amount, ">0".
  • From cell: =AVERAGEIFS(B:B, A:A, E2).

People also ask

  • AVERAGEIFS #DIV/0!? — No rows matched — IFERROR to show blank.
  • AVERAGEIFS with dates? — Use DATE or >= and <= bounds on date column.
  • Wildcards? — * and ? supported in text criteria like COUNTIFS.
  • AVERAGEIFS vs filtered AVERAGE? — SUBTOTAL on filtered list vs formula criteria.

Common errors

  • Ranges must be same size
  • #DIV/0! if no matches

Use cases

  • Conditional averages
  • Performance metrics
  • Filtered statistics

Frequently asked questions

  • How do I calculate average for a specific category and date range? Use multiple criteria: =AVERAGEIFS(Sales, Category, "Electronics", Date, ">="&DATE(2024,1,1), Date, "<="&DATE(2024,12,31)). This averages Electronics sales for 2024 only.
  • Why does AVERAGEIFS return #DIV/0! error? AVERAGEIFS returns #DIV/0! when no cells match all criteria. Wrap in IFERROR: =IFERROR(AVERAGEIFS(...), 0) or =IFERROR(AVERAGEIFS(...), "No data"). Check that your criteria are correct and data exists.
  • Can I use wildcards in AVERAGEIFS criteria? Yes! Use * for any characters, ? for single character. =AVERAGEIFS(Sales, Product, "*Phone*") averages sales for all products containing 'Phone'. Use ~* or ~? to match literal asterisk or question mark.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use AVERAGEIFS

  • Conditional averages — common Statistical scenario for AVERAGEIFS.
  • Performance metrics — common Statistical scenario for AVERAGEIFS.
  • Filtered statistics — common Statistical scenario for AVERAGEIFS.

AVERAGEIFS in the Statistical category

  • Browse all Statistical functions at /categories/statistical/ for related formulas.
  • AVERAGEIFS syntax: =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)
  • average_range (required): Cells to average
  • criteria_range1 (required): First range to evaluate
  • criteria1 (required): First criteria
  • Confirm AVERAGEIFS arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm AVERAGEIFS arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • AVERAGEIF (/functions/averageif/): Returns the average of cells that meet a single criterion.
  • AVERAGE (/functions/average/): Returns the average (arithmetic mean) of the arguments.
  • SUMIFS (/functions/sumifs/): Adds cells that meet multiple criteria across multiple ranges.
  • COUNTIFS (/functions/countifs/): Counts cells that meet multiple criteria across multiple ranges.

Comparisons involving this function

  • SUMIF vs SUMIFS (/compare/sumif-vs-sumifs/): Use SUMIF for one condition. Use SUMIFS when totals depend on multiple criteria such as region, product, date, or status.
  • COUNTIF vs COUNTIFS (/compare/countif-vs-countifs/): Use COUNTIF for a single condition. Use COUNTIFS when the count depends on multiple filters.
  • AVERAGEIF vs AVERAGEIFS (/compare/averageif-vs-averageifs/): Use AVERAGEIF for one condition. Use AVERAGEIFS when the average depends on two or more filters such as region, product, and date range.

Errors to watch for

  • Ranges must be same size — review causes on linked error pages in the directory.
  • #DIV/0! if no matches — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter AVERAGEIFS on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

AVERAGEIFS worked examples to copy

  • =AVERAGEIFS(D2:D10, B2:B10, "Sales", C2:C10, ">1000") — Average sales over 1000. Expected result: Average value.

AVERAGEIFS reference summary for crawlers and offline review

  • AVERAGEIFS belongs to the Statistical category in Excel. Returns the average of cells that meet multiple criteria.
  • Full syntax: =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...). Open /functions/averageifs/ for parameters, FAQs, and related pages.
  • Common mistakes: Ranges must be same size; #DIV/0! if no matches
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.