Excel AVERAGEIF Function

AVERAGEIF calculates the average of numbers in a range, but only for the cells that meet a condition you specify. It returns a single number. The default behavior worth knowing up front: if you skip the third argument, AVERAGEIF averages the same range you're checking the condition against, which is fine some of the time and wrong the rest of the time.

Syntax

=AVERAGEIF(range, criteria, [average_range])

Parameter	Required	Description
range	Yes	The cells checked against your condition. Can hold numbers, text, or dates.
criteria	Yes	The condition a cell in range must meet. Can be a number, text, a cell reference, or an expression like ">500".
average_range	No	The cells actually averaged when a match is found. If omitted, AVERAGEIF averages range itself.

Basic Example

You're tracking sales orders with regions in column A, rep names in column B, and order amounts in column C. To find the average order size for the East region:

=AVERAGEIF(orders[Region], "East", orders[Amount])

// orders[Region]  = the range checked for "East"
// "East"          = the condition
// orders[Amount]  = the range averaged when a row matches

AVERAGEIF scans every row in orders[Region]. Wherever it finds "East," it pulls the matching value from orders[Amount] and includes it in the average. Rows for any other region are skipped entirely, not counted as zero.

How AVERAGEIF Works

It evaluates exactly one condition

AVERAGEIF checks a single criterion against a single range. If a row needs to satisfy two conditions at once, such as region equals "East" and amount exceeds $500, AVERAGEIF can't do that on its own. That's the job of AVERAGEIFS.

Blank cells are skipped, zero values are counted

This distinction trips people up constantly. A blank cell in average_range is excluded from the calculation entirely; it doesn't lower the average, it just doesn't exist as far as AVERAGEIF is concerned. A cell containing the number 0, on the other hand, counts as a real value and pulls the average down.

Criteria supports operators and wildcards

You can use comparison operators like ">100", "<>0", or "<="&D2 where D2 holds a threshold. Wildcards work too: "East*" matches "East," "Eastside," and "East Coast" alike. Wrap operator criteria in quotes; wrap cell references in the concatenation shown above.

Text matching isn't case-sensitive

"east", "East", and "EAST" are treated as identical criteria. If you need case-sensitive matching, AVERAGEIF isn't the right tool. You'd need an array formula built around EXACT.

Common Use Cases

Average sales by rep name

You want the average deal size closed by a specific salesperson.

=AVERAGEIF(reps, "Priya Nair", deal_amounts)   // averages only Priya's closed deals

Average orders above a dollar threshold

You're filtering out small test orders and want the average of everything over $250.

=AVERAGEIF(order_amounts, ">250")

// order_amounts checked against the condition
// no average_range needed because we're averaging the same values we're filtering

Average while excluding zero-value rows

Zero-dollar rows in a discount tracker skew the average down. Excluding them entirely gives a cleaner number.

=AVERAGEIF(discount_amounts, "<>0")   // ignores rows where the discount is exactly 0

Average by a value in another cell

Instead of hardcoding a region name, reference a cell so the formula updates when the input changes.

=AVERAGEIF(orders[Region], H2, orders[Amount])

// H2 holds the region name typed in by the user
// updates automatically if H2 changes

Handling Errors

AVERAGEIF returns #DIV/0! when no cells match the criteria. Averaging an empty set is mathematically undefined, so Excel throws the error instead of returning 0.

Common causes of #DIV/0!:

• No rows in range match criteria at all
• A typo in the criteria text ("Eastt" instead of "East")
• The criteria references a blank cell, so nothing can match
• average_range is entirely blank in the matching rows

=IFERROR(AVERAGEIF(orders[Region], "West", orders[Amount]), "No matches")
=IFERROR(AVERAGEIF(orders[Region], H2, orders[Amount]), "No matches for that region")

Notes & Gotchas

What is the difference between AVERAGE and AVERAGEIF in Excel?

AVERAGE calculates the mean of every numeric value in a range, no conditions attached. AVERAGEIF adds a filter step first: only cells that meet your criteria get included in the calculation. If you want the average of everything, use AVERAGE. If you want the average of a subset, use AVERAGEIF.

What is the difference between AVERAGEIF and AVERAGEIFS?

AVERAGEIF handles one condition. AVERAGEIFS handles multiple conditions at once, up to 127 of them, and requires a separate criteria range for each one. The argument order also flips: AVERAGEIF puts average_range last, while AVERAGEIFS puts it first, right after the function name.

=AVERAGEIF(range, criteria, average_range)
=AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

How do you use AVERAGEIF with multiple criteria or an OR condition?

AVERAGEIF can't natively handle an OR condition across two different values in the same field. The workaround is to add two AVERAGEIF results together and divide by the combined count, or restructure the logic with SUMPRODUCT. For a true AND across two different fields, switch to AVERAGEIFS instead, since AVERAGEIFS only evaluates AND logic, never OR, across its condition pairs.

// Average orders where region is "East" OR "West"
=(SUMIF(orders[Region],"East",orders[Amount])+SUMIF(orders[Region],"West",orders[Amount]))/(COUNTIF(orders[Region],"East")+COUNTIF(orders[Region],"West"))

How do you calculate an average in Excel while excluding blank or zero values?

Blank cells are already excluded automatically. AVERAGEIF simply skips them without any extra syntax needed. To exclude zeros too, add "<>0" as your criteria alongside whatever other condition you're checking, or use it as the sole criteria if you just want to drop zeros from an otherwise unfiltered range.

How do you use AVERAGEIF with text criteria?

Type the text directly in quotes, like "Marketing" or "North Region", and AVERAGEIF matches it exactly, ignoring case. To match a partial string, add a wildcard: "North*" catches "North Region" and "Northeast" both. Reference a cell instead of hardcoding text if the value needs to change dynamically.

Does AVERAGEIF work with array constants instead of cell ranges?

No. range and average_range must be actual cell references on a worksheet, not arrays typed directly into the formula or generated by another function like FILTER. If you need array-based conditional averaging, use AVERAGE combined with an IF array formula, or AVERAGE with FILTER on Excel 365.

Related Functions

Function	Use this when...
SUMIF	You need a conditional sum instead of a conditional average.
COUNTIF	You need to count matching cells rather than average them.