AVERAGEIF Formula in Excel: Syntax and Practical Examples

Formulas & functions · Updated November 21, 2026

In Excel, use =AVERAGEIF(range, criteria, [average_range]) to average numbers that meet one condition. For example, with departments in A2:A6 and sales in B2:B6, =AVERAGEIF(A2:A6,"East",B2:B6) averages sales for rows whose department is East.

What AVERAGEIF Does

AVERAGEIF combines a test with an average. It checks each cell in a criteria range, identifies the cells that satisfy the condition, then averages the corresponding numeric values. This is useful when a worksheet contains many records and you need a conditional average, such as average sales for one region, average test scores above a threshold, or average costs for a particular category.

Unlike a simple average, AVERAGEIF does not require you to filter or rearrange the source data first. You can change the criterion and let the formula recalculate. The function is available in current Microsoft Excel for Microsoft 365 on Windows, and the same function syntax works in Excel for Mac and Excel for the web.

For example, suppose A2:A6 contains East, West, East, North, and East, while B2:B6 contains 120, 200, 90, 160, and 150. The East values total 360, and there are three of them, so their average is 120. The formula below returns that result.

With departments in A2:A6 and sales in B2:B6, enter:

=AVERAGEIF(A2:A6,"East",B2:B6)

Syntax and the Three Arguments

The function has one required range, one required criterion, and one optional average range:

  • range is the group of cells Excel checks against the condition. In the example, this is A2:A6, the department column.
  • criteria specifies what must be true for a row to be included. It can be text, a number, an expression in quotation marks, or a reference to a cell containing the condition.
  • average_range is the group of numbers Excel averages when the matching condition is met. It is optional. If you leave it out, Excel checks and averages the same range.

The square brackets around average_range in the syntax mean it is optional; do not type those brackets in the worksheet formula. In typical data, the criteria range and average range have matching rows, so each test lines up with the value to average. Keep the ranges aligned and the same size to make the relationship clear, even though Excel can handle some differently sized ranges by anchoring the calculation to the average range’s upper-left cell.

Basic Examples: Text and Numbers

For a text condition, put the text in quotation marks when typing it directly in the formula. The East example above uses an exact text criterion. Excel’s text comparisons are not case-sensitive, so East and EAST are treated alike. If the criterion is in a worksheet cell, refer to that cell instead of typing the text into the formula.

With departments in A2:A6, sales in B2:B6, and the word East in D2, enter:

=AVERAGEIF(A2:A6,D2,B2:B6)

This also returns 120. A cell reference makes the formula easier to reuse: change D2 to West, for instance, and the formula calculates the West average without editing its structure.

A numeric condition can use the average range itself. With scores 72, 85, 91, 68, and 84 in C2:C6, enter:

=AVERAGEIF(C2:C6,">=80")

Because average_range is omitted, Excel tests and averages C2:C6. The qualifying scores are 85, 91, and 84; their total is 260, so the result is 86.6667 (or 86.67 when displayed to two decimal places). This is a common way to average values above or below a cutoff.

Using Operators, Cell References, and Wildcards

For comparisons, place the operator and number together inside quotation marks. Common operators are greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>). If you want the threshold to come from a cell, join the operator text to the cell reference with the ampersand concatenation symbol.

With scores in C2:C6 and the threshold 80 in D2, enter:

=AVERAGEIF(C2:C6,">="&D2)

This returns the average of scores greater than or equal to the value in D2. If D2 changes to 90, the qualifying set changes automatically. A frequent mistake is to write the operator and cell reference as one quoted phrase; Excel then treats the cell reference as text rather than using its value.

For text criteria, AVERAGEIF supports two wildcards:

  • An asterisk (*) matches any sequence of characters, including no characters. A criterion such as North* can match North, North-1, or North Region.
  • A question mark (?) matches one character. A criterion such as A? matches two-character entries beginning with A.
  • A tilde (~) escapes a wildcard when you need to search for a literal asterisk or question mark. For example, ~* represents an asterisk character rather than the wildcard.

Wildcards are useful with inconsistent labels, but they broaden the match. Before relying on a result, check that the pattern includes the records you intend and does not include similarly named categories by accident.

Working with Dates and Blank or Zero Values

Excel stores dates as serial numbers, so date criteria can use comparison operators. It is usually more reliable to put a real Excel date in a cell and build the criterion from that reference than to type a date string into the formula. Typed date strings can be interpreted differently depending on regional date settings.

With dates in A2:A5, amounts in B2:B5, and a real Excel date in D2, enter:

=AVERAGEIF(A2:A5,">="&D2,B2:B5)

For example, if the four dates are January 5, January 15, February 2, and February 20, the amounts are 100, 200, 300, and 400, and D2 is January 15, the qualifying amounts are 200, 300, and 400. Their average is 300. This condition includes D2 itself because it uses greater than or equal to.

AVERAGEIF ignores blank cells and text in the cells it averages, but it includes numeric zero. That distinction can matter: a zero may represent a genuine result, while a blank may mean the record is not yet complete. If you do not want zeros included, use a condition that excludes them or consider AVERAGEIFS when the data needs several tests. Do not replace meaningful zeros with blanks just to change an average; first decide what the underlying data represents.

AVERAGEIF Compared with AVERAGEIFS

AVERAGEIF handles one condition. When a calculation must meet two or more conditions, use AVERAGEIFS, whose argument order starts with the range to average, followed by pairs of criteria ranges and criteria. This order differs from AVERAGEIF, so copying a formula and merely adding another condition can lead to an incorrect argument order.

With departments in A2:A6, sales in B2:B6, and channel labels in C2:C6, enter:

=AVERAGEIFS(B2:B6,A2:A6,"East",C2:C6,"Retail")

Using the earlier sales data, if the East rows have sales of 120, 90, and 150, and all three are Retail, the formula returns 120. If only some East rows are Retail, it averages only those rows that satisfy both tests. Use AVERAGEIF for a single condition and AVERAGEIFS for multiple conditions; the latter is supported in current Excel versions, including Microsoft 365.

Common Problems and How to Fix Them

A result that seems wrong often comes from a mismatch between what the condition tests and what the formula averages. Check that the criteria range contains the labels or values you intend to test, that average_range points to the corresponding numbers, and that the condition is written with the correct operator. Also check for leading or trailing spaces in text labels: “East” and “East ” can behave as different entries.

  • #DIV/0!: Excel found no qualifying numeric values to average. Confirm that at least one cell meets the criterion and that the corresponding average cell contains a number rather than text or a blank.
  • Unexpectedly low average: Check whether some matching rows have blank or text values in average_range, since those are not included in the numeric average.
  • Unexpectedly high or low average: Check whether zeros should count, whether the comparison should be inclusive (for example, >= rather than >), and whether a wildcard is matching extra labels.
  • Formula not accepted: Make sure parentheses and quotation marks are balanced. Excel installations using regional settings that require semicolons may display semicolons as argument separators instead of commas; follow the separator your Excel formula editor expects.

When a criterion is stored in a cell, inspect the actual cell value and its type. A number stored as text may not compare as expected with a numeric threshold. If you suspect hidden spaces, clean or standardize the source labels rather than compensating with a broader wildcard that could match unintended records.

Frequently Asked Questions

What is the difference between AVERAGE and AVERAGEIF?
AVERAGE returns the mean of supplied numeric values without applying a condition. AVERAGEIF first selects values based on one criterion, then averages the corresponding numbers.

Can AVERAGEIF use more than one condition?
No. It accepts one criterion. Use AVERAGEIFS when a calculation must satisfy multiple conditions, such as a department and a sales channel.

Does AVERAGEIF count zero values?
Yes. Zero is a number, so it is included in the average when its row meets the criterion. Blank cells and text in the average range are ignored.

Why does AVERAGEIF return #DIV/0!?
Usually, no rows meet the condition, or the matching rows have no numeric values in the average range. Check the criterion, its source data, and the corresponding average cells.

Can I use a date as an AVERAGEIF criterion?
Yes. Use a real Excel date value, often stored in a cell, and combine it with a comparison operator when needed. This avoids ambiguity from date strings that may be interpreted differently under regional settings.

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.