AVERAGEIF Excel Range: Syntax, Examples, and Common Fixes

Formulas & functions · Updated November 18, 2026

In Excel, the range in AVERAGEIF is the group of cells Excel checks against your condition; the optional average_range is the group of numeric cells it averages when that condition is met. The function is written =AVERAGEIF(range, criteria, [average_range]) in descriptions, but the brackets mean the third argument is optional—not characters to type into a formula.

What the AVERAGEIF range arguments mean

AVERAGEIF uses one condition to select values for an average. Its arguments have distinct jobs:

  • range is required. Excel evaluates each cell in this range against the criterion.
  • criteria is required. It can be a number, text, expression, cell reference, or pattern containing wildcards.
  • average_range is optional. When supplied, Excel averages corresponding cells in this range for rows or positions whose cells in range meet the criterion.

For example, if column A contains regions and column B contains sales amounts, A2:A20 can be the test range and B2:B20 the average range. The function checks each region in column A, then averages sales from the corresponding rows in column B. The range is not a limit on how many cells can be averaged; it is the condition-checking area.

If you omit average_range, Excel checks and averages the same range. This is useful when the values being tested are also the values to average, such as finding the average of numbers greater than a threshold.

Basic syntax and a worked example

Suppose A2:A6 contains North, South, North, East, and North, while B2:B6 contains 100, 200, 150, 300, and 250. To average sales only for North, the formula is:

With region names in A2:A6 and sales in B2:B6, enter: =AVERAGEIF(A2:A6,"North",B2:B6). The result is 166.6667, because the matching amounts are 100, 150, and 250, and (100 + 150 + 250) ÷ 3 = 166.6667.

In Microsoft 365 for Windows, type the formula in an empty cell and press Enter. Excel calculates the result; use the Home tab’s number-format controls if you want to show fewer decimal places. The same formula structure works in Excel for Mac and Excel for the web. Depending on regional settings, Excel may display semicolons instead of commas between arguments; use the separator shown by your installation.

When the data is arranged in a table, selecting the relevant cells with the mouse while entering a formula can help avoid range-typing errors. Check that both ranges cover the intended records, including the same first and last data rows.

When to omit average_range

Leaving out the third argument is appropriate when the numbers you want to average are in the same cells Excel is checking. For instance, a list of test scores can be checked for scores above 20 and averaged directly. Empty cells and text values in the averaging range are not treated as numeric observations; a numeric zero is included in the average.

With scores in C2:C7 containing 10, 20, 30, a blank, 0, and 40, this formula averages values greater than 20:

With the scores in C2:C7, enter: =AVERAGEIF(C2:C7,">20"). The result is 35, from (30 + 40) ÷ 2. The blank and zero do not meet the condition, and 10 and 20 are below the threshold.

The distinction matters when a condition column contains text or categories. If you omit average_range in the North-and-sales example, Excel would try to average the region cells rather than the sales figures. Specify the sales range whenever the cells to test and the numbers to average are different.

Writing criteria for numbers, text, and dates

A criterion can be an exact match or a comparison. For text, put the text in quotation marks in the formula. For numeric comparisons, put the operator and value together in a quoted string, such as ">50" or "<=100". The supported comparison operators include greater than, less than, equal to, and their inclusive forms.

If the threshold is stored in a cell, join the operator to the cell reference with &. With values in B2:B6 and a threshold in D1, this example averages values at least as large as the threshold:

With amounts in B2:B6 and the numeric cutoff in D1, enter: =AVERAGEIF(B2:B6,">="&D1). If D1 contains 100 and the matching values are 100, 150, and 200, the result is 150.

Date conditions work on the same principle because Excel stores actual dates as numeric values. Use a real Excel date in the cutoff cell rather than text that merely looks like a date. With dates in D2:D5, corresponding amounts in E2:E5, and a cutoff date in F1, this formula averages amounts on or after the cutoff:

With transaction dates in D2:D5, amounts in E2:E5, and the cutoff date in F1, enter: =AVERAGEIF(D2:D5,">="&F1,E2:E5). If the qualifying amounts are 40 and 60, the result is 50. The ampersand combines the comparison operator with the date value in F1.

For a blank criterion, Excel can check for empty cells; a criterion such as "<>" checks for nonblank cells. A blank check is not a substitute for testing whether a cell contains a numeric value: a nonblank cell might contain text, and text in the average range is not a number to average.

Matching cells with wildcards

For text criteria, * represents any number of characters and ? represents one character. These are useful when the entries share a prefix, suffix, or partial name. For example, if A2:A5 contains East – retail, East – online, West – retail, and North, and B2:B5 contains 100, 200, 300, and 400, the following formula matches both East entries:

With category labels in A2:A5 and amounts in B2:B5, enter: =AVERAGEIF(A2:A5,"East*",B2:B5). The result is 150, from (100 + 200) ÷ 2.

Use a tilde to search for a literal wildcard character in text: ~* represents an asterisk and ~? represents a question mark. Without the tilde, those characters are interpreted as pattern symbols. Wildcards apply to text matching; they are not a way to make numeric criteria approximate.

How range alignment works

The clearest setup is to make range and average_range the same size and shape, with corresponding records on the same rows. For example, A2:A100 and B2:B100 are an easy-to-audit pair: A2 determines whether B2 is included, A3 determines whether B3 is included, and so on.

Excel does not require the two arguments to have identical dimensions in every case. If they differ, Excel uses the top-left cell of average_range as the starting point and maps the cells to the dimensions of range. That behavior can be surprising: a short average range may extend beyond the cells you expected to use. Matching the ranges avoids ambiguity and makes formulas easier to maintain.

Do not include a header in only one of the ranges. If the condition range begins at row 2 but the average range begins at row 1, the corresponding records will be offset. For long datasets, using equal-sized table columns or consistently bounded ranges reduces this risk.

Common errors and how to fix them

A frequent result is #DIV/0!. It usually means no qualifying numeric values were available to average. The criterion might be misspelled, the condition range might point to the wrong column, or matching rows might contain blanks or text in the average range. Check the matching records and confirm that the amounts are stored as numbers.

  • Unexpectedly low or high average: Confirm that the average range points to the numeric measure, not the category column, and that both ranges start on corresponding rows.
  • A comparison seems ignored: Put operators such as > or >= inside the criterion string, or join the operator to a cell reference with &.
  • A number stored as text is missed: Convert the source values to real numbers. Formatting a text value to look like a number does not necessarily change its underlying type.
  • A date condition returns an unexpected result: Check that the date cells and the cutoff are actual Excel dates, not text strings, and that the comparison is written as a joined operator and cell reference.
  • The formula is rejected: Check that quotation marks and parentheses are paired, and use the argument separator required by the computer’s regional settings.

For a formula that might legitimately find no matches, an error-handling function can display a more informative message. With region names in A2:A20 and sales in B2:B20, for example, =IFERROR(AVERAGEIF(A2:A20,"North",B2:B20),"No numeric matches") returns the average when possible and the message if the calculation errors. Use this deliberately: it can also hide other formula errors, so it is still worth checking the source data.

AVERAGEIF compared with related functions

AVERAGEIF handles one condition. If a calculation must satisfy two or more conditions—such as Region equals North and Month equals January—use AVERAGEIFS. Its argument order differs: it takes the average range first, followed by pairs of criteria ranges and criteria. That reversal is a common source of copied-formula mistakes.

SUMIF uses a similar condition-and-range pattern but adds matching values instead of averaging them. AVERAGE averages a set of values without applying a condition. Use whichever function expresses the calculation directly; building an average from a sum and a count is usually unnecessary when AVERAGEIF fits.

A filter can be useful when you want to inspect the qualifying records rather than return only an average. Filtering changes which rows are displayed, while AVERAGEIF calculates from its stated ranges regardless of whether rows are currently hidden by a filter. For multiple conditions, choose AVERAGEIFS rather than trying to combine several independent AVERAGEIF results unless that is specifically the intended calculation.

Frequently Asked Questions

What is the range in AVERAGEIF?
The first argument is the range Excel checks against the criterion. It is often a category or date column. The optional third argument identifies the corresponding numbers to average.

Can AVERAGEIF use a different range to average?
Yes. Supply the numeric cells as average_range. Keep it aligned with the condition range so each matching record points to its own value.

What happens if I leave out average_range?
Excel uses the condition range for the average too. This works when the tested cells are themselves the numeric values you want to average.

Why does AVERAGEIF return #DIV/0!?
There may be no cells meeting the criterion that contain numeric values to average. Check the criterion, the condition range, and the data type and contents of the average range.

Can AVERAGEIF check more than one condition?
Not by itself; it accepts one criterion. Use AVERAGEIFS when an average must meet multiple conditions, such as a matching region and a date range.

Do the two ranges need to be the same size?
They should be the same size and shape for predictable, readable formulas. Excel can handle differing dimensions by mapping from the top-left of the average range, but that can produce results that are easy to misinterpret.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.