Excel AVERAGEIFS Function

AVERAGEIFS calculates the average of cells in a range that meet two or more conditions you specify. Every condition must be true for a row to count, no exceptions. If no row satisfies all the conditions, the formula returns #DIV/0! instead of zero, which trips up a lot of dashboards that don't expect it.

Available in: Excel 2007 and later, including Excel 365, Excel 2021, and Excel 2019.

Syntax

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

Parameter	Required	Description
`average_range`	Yes	The numbers you actually want averaged. AVERAGEIFS ignores blank cells, text, and TRUE/FALSE values here. It counts zero values.
`criteria_range1`	Yes	The first range to test. Must be the same size and shape as `average_range`.
`criteria1`	Yes	The condition applied to `criteria_range1`. Can be a number, text, a comparison operator like `">50"`, or a cell reference.
`criteria_range2, criteria2`	No	Add as many additional range/condition pairs as you need. Excel supports up to 127.

⚠

average_range and every criteria_range must be actual cell ranges. AVERAGEIFS won't accept an array built from a formula, and it won't let you enter the formula at all if you try. If you need to average something calculated on the fly, use AVERAGE with an array formula or SUMPRODUCT instead.

Basic Example

You're running a sales tracker with orders logged in named ranges: region (column B), month (column C), and sales_amount (column D). You want the average order value for the West region in March.

=AVERAGEIFS(sales_amount, region, "West", month, "March")

// sales_amount = the numbers being averaged
// region       = first range to test
// "West"       = condition for the region range
// month        = second range to test
// "March"      = condition for the month range

AVERAGEIFS scans every row. It keeps only the ones where region equals "West" and month equals "March," then averages the corresponding values in sales_amount. If West had orders of 445, 512, and 398 in March, the formula returns 451.67. AVERAGEIFS excludes rows from other regions or months entirely. It doesn't count them as zero.

How AVERAGEIFS Works

It uses AND logic across every condition

AVERAGEIFS counts a row only when every condition is true at once. There's no built-in way to average rows that match condition A or condition B. AVERAGEIFS was built for narrowing down, not broadening out. See the gotcha below for a workaround.

Zero counts, but blanks and text don't

In average_range, AVERAGEIFS includes a cell containing 0 in the average. It skips a blank cell, a text entry, or a TRUE/FALSE value, as if that row never existed. This distinction matters more than it sounds. It's the difference between "average sales, treating missing entries as zero" and "average sales, ignoring rows with no data."

Every range must match average_range in size and shape

If average_range is D2:D500, every criteria_range also has to be 499 rows tall, starting and ending on the same rows. Mismatched ranges don't quietly produce a wrong average. They throw #VALUE! immediately.

Criteria accept operators, wildcards, and cell references

You can hardcode ">100" directly into a criteria argument. You can also use "*east*" to match any text containing "east." But when a criteria value comes from a cell reference and needs an operator, you have to concatenate them:

=AVERAGEIFS(sales_amount, sales_amount, ">"&E2)

// ">"&E2 = the operator joined to a cell reference with concatenation

Excel can't evaluate ">" and a cell reference as a single unit unless you join them with & first. Drop the concatenation and the formula either errors out or silently returns the wrong thing.

Common Use Cases

Average performance score by department and status

You're reviewing employee reviews and want the average score for Sales reps marked "Active."

=AVERAGEIFS(score, department, "Sales", status, "Active")   // average score, Sales dept, active reps only

Average order value within a date range

Two criteria pairs on the same column let you bracket a range of dates.

=AVERAGEIFS(sales_amount, order_date, ">="&DATE(2026,1,1), order_date, "<="&DATE(2026,3,31))

// order_date used twice: once for the lower bound, once for the upper bound
// DATE(2026,1,1) and DATE(2026,3,31) = the boundaries of Q1

Average shipping cost by region, using a wildcard

Match any region name containing "coast" without listing each one.

=AVERAGEIFS(shipping_cost, region, "*coast*")   // matches East Coast, West Coast, Gulf Coast

Handling Errors

AVERAGEIFS throws two errors in practice, and both are worth understanding rather than just suppressing.

#DIV/0! shows up when no row satisfies all the criteria, or when every matching cell in average_range is blank or non-numeric. This is the one most people mistake for a bug. It's actually correct behavior: Excel can't average zero rows.

#VALUE! appears when a criteria_range doesn't match average_range in size. A common cause is copying the formula down and accidentally shifting one range while locking another.

=IFERROR(AVERAGEIFS(sales_amount, region, "West", month, "March"), "No matching orders")

✓

Don't wrap AVERAGEIFS in IFERROR just to hide a real problem. If you're consistently getting #DIV/0!, check your criteria spelling and range alignment first. IFERROR should catch the occasional empty result, not mask a formula that's broken.

Notes & Gotchas

Why does AVERAGEIFS return #DIV/0!?

Because no row in the data matched every condition, or because average_range had no numeric values among the rows that did match. Excel can't compute an average of zero numbers, so it returns #DIV/0! instead of silently showing 0. Check for typos in your criteria text and confirm the criteria ranges actually overlap with real data.

Does AVERAGEIFS support OR logic between criteria?

No. AVERAGEIFS only evaluates conditions with AND logic; it includes a row only when that row satisfies every pair. To average rows that match condition A or condition B, run two separate AVERAGEIFS formulas and combine them, or switch to an array formula like AVERAGE(IF((cond1)+(cond2), range)) entered with Ctrl+Shift+Enter on older versions.

Can AVERAGEIFS exclude zero values automatically?

No, and this catches people off guard. Zero is a valid number, so AVERAGEIFS includes it in the average by default. If you want zeros excluded, add the same range twice: once as average_range and once more as a criteria_range with the condition "<>0".

Does AVERAGEIFS work with wildcards in text criteria?

Yes. Use * to match any number of characters and ? to match a single character, the same wildcards SUMIFS and COUNTIFS accept. "*east*" matches "East Coast" and "Northeast," while "20?" matches any three-character code starting with "20."

What happens if a criteria value comes from a cell reference and includes an operator?

You have to concatenate the operator with the reference using &, as in "<"&F2. Excel evaluates the cell reference or formula first to get a value, then joins it with the operator as text. Typing <F2 directly into the criteria argument doesn't work; Excel treats it as literal text, not a comparison.

Does AVERAGEIFS work with dates?

Yes, and it's one of the more common uses. Bracket a date range with two criteria pairs on the same column: one using ">=" for the start date and one using "<=" for the end date. Wrap both dates in DATE() or reference them from cells. Typing a raw date string as criteria can cause Excel to misread it depending on regional settings.

Related Functions

Function	Use this when...
`AVERAGEIF`	You only need to test one condition instead of several.
`SUMIFS`	You want a total instead of an average, using the same multi-condition logic.
`COUNTIFS`	You want to count matching rows rather than average a value.
`XLOOKUP`	You need to return a single value tied to a match, not an average across many rows.