AVERAGEIF in Excel: How It Works and When to Use It

Formulas & functions · Updated November 20, 2026

AVERAGEIF in Excel calculates the average of numbers that meet one condition. Give it a range to check, a criterion to apply, and—if the values to average are elsewhere—the range containing those values.

What AVERAGEIF does

AVERAGEIF is a conditional averaging function: it checks cells against a rule, then averages the corresponding numeric values for the cells that pass. It is useful when a normal AVERAGE would include too much data, such as when you need the average order amount for one region, the average score above a threshold, or the average cost for a particular product category.

The function has one condition. For example, it can select rows where the region is North, or amounts greater than 100. It cannot, by itself, require two different conditions at once; for that, use AVERAGEIFS. AVERAGEIF is available in Microsoft Excel, including Microsoft 365 for Windows. The same function is also available in Excel for Mac and Excel for the web, though the formula separator may depend on your regional settings.

Syntax and the role of each argument

AVERAGEIF has three arguments. The first is the range Excel evaluates, the second is the criterion that determines which cells qualify, and the optional third is the range containing the numbers to average. If you leave out the third argument, Excel averages numeric cells in the first range that meet the criterion.

  • Range: The cells Excel checks against the criterion.
  • Criteria: The condition to apply. It can be a number, text, a comparison such as greater than a value, or a reference to a cell containing the criterion.
  • Average_range: The cells to average when the corresponding cell in the first range meets the condition. It is optional.

When you supply both ranges, keep them the same shape and aligned row by row. For example, a condition checked in A2:A20 should normally be paired with values to average in B2:B20. If the ranges are offset or have different dimensions, the results can be confusing; matching ranges make the row-by-row relationship clear.

A basic example with a separate average range

Suppose A2:A7 contains region names and B2:B7 contains order amounts. The rows are North—120, South—80, North—150, West—200, North—90, and South—100. To average the amounts for North, enter this formula in any empty cell:

With region names in A2:A7 and order amounts in B2:B7, the formula is =AVERAGEIF(A2:A7,"North",B2:B7). It returns 120, because the qualifying amounts are 120, 150, and 90, whose sum is 360 and whose count is 3.

This example shows why the two-range form is useful: Excel checks the labels in column A but averages the corresponding numbers in column B. If instead you had numbers in A2:A7 and wanted to average only those meeting a condition, you could omit the third argument and use that same range for both jobs.

Writing criteria for numbers, text, and references

Criteria are flexible, but the way you write one depends on the condition. A simple text criterion can be enclosed in straight double quotation marks, as in the North example. Text comparisons are not case-sensitive, so North and NORTH match the same text for this function.

For a numeric test, you can use a number directly or put a comparison operator and value inside quotation marks. With amounts in B2:B7, this formula averages the values greater than 100:

With the order amounts in B2:B7, =AVERAGEIF(B2:B7,">100") returns about 156.67. The qualifying numbers are 120, 150, and 200; their total is 470, and 470 divided by 3 is 156.6667.

The comparison operators you will commonly use are equal to (=), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>). For example, the criterion “>=100” includes 100 as well as larger numbers. In a criterion argument, the operator and value are usually written together as text.

If the threshold is stored in a cell, join the operator to the cell reference with an ampersand. With amounts in B2:B7 and a threshold of 100 in D2, use =AVERAGEIF(B2:B7,">"&D2). The result is again about 156.67. This makes it easy to change the threshold without editing the formula itself.

For text patterns, AVERAGEIF recognizes wildcards: an asterisk (*) stands for any number of characters, and a question mark (?) stands for one character. A tilde (~) lets you search for a literal asterisk or question mark. For instance, a criterion of “North*” matches text beginning with North, including North-East. Wildcards are for text matching, not for expressing numeric ranges.

Dates, blanks, and what gets averaged

Excel stores real dates as serial numbers, so date criteria can use the same comparison approach as numeric criteria. A useful practice is to build the criterion from a date value instead of typing an ambiguous date string. That reduces the chance that a date will be interpreted differently under another regional date format. Make sure the cells being checked contain actual Excel dates rather than text that merely looks like a date.

For example, with dates in A2:A100, amounts in B2:B100, and a date boundary in D2, a formula can average amounts for dates on or after that boundary: =AVERAGEIF(A2:A100,">="&D2,B2:B100). The condition applies to the date column, while the corresponding amount cells are averaged. Ensure D2 contains a valid Excel date.

AVERAGEIF averages numbers, not every cell that meets the criterion. Text and blank cells in the average range are not numeric values to include in the average; a numeric zero is included and can affect the result. That distinction matters in reports: a blank may mean no recorded value, while zero may be a genuine measured amount. Decide how your data represents missing information before interpreting the average.

When to use AVERAGEIFS instead

Use AVERAGEIF when one condition is enough. If you want the average order amount for North during January, you need to test both a region and a date range. AVERAGEIFS is designed for multiple conditions and takes the average range first, followed by pairs of criteria ranges and criteria. Its argument order differs from AVERAGEIF, so do not simply add another condition to the end of an AVERAGEIF formula.

With regions in A2:A7, dates in C2:C7, amounts in B2:B7, and a start date in D2 and end date in E2, an AVERAGEIFS formula could average North orders within those dates. Use =AVERAGEIFS(B2:B7,A2:A7,"North",C2:C7,">="&D2,C2:C7,"<="&E2). The date range is tested twice: once for dates at or after the start and once for dates at or before the end. Keep the average range and each criteria range aligned row by row.

AVERAGEIFS returns an average only for rows that meet all its criteria. For a single condition, AVERAGEIF is shorter and easier to read. For multiple conditions, AVERAGEIFS is usually clearer than building a more complicated workaround.

Common problems and how to diagnose them

  • #DIV/0!: This commonly appears when no cells satisfy the criterion, or when qualifying rows contain no numeric values in the average range. Check that the criterion matches the data and that the corresponding average cells contain numbers.
  • The result is unexpectedly high or low: Check that the average range lines up with the criteria range. Also inspect whether zeros are valid values or placeholders for missing data.
  • A text condition does not match: Look for leading or trailing spaces, spelling differences, or labels that contain extra words. Wildcards can help when a partial text match is intended, but they should not conceal inconsistent data.
  • A numeric or date condition behaves oddly: Confirm that the underlying cells are stored as numbers or dates, not text. When the condition comes from another cell, join the comparison operator and reference with &.
  • The formula is rejected: Check the commas and quotation marks, and confirm that every opening parenthesis has a closing one. Some regional settings use semicolons as formula separators instead of commas; use the separator Excel expects in that installation.

When troubleshooting, test the condition separately by filtering the source data or checking a few rows manually. That helps distinguish a formula issue from a data-quality issue. Also verify the expected qualifying values and calculate their total and count; the average should equal the total divided by the count of numeric values included.

Practical ways to keep formulas reliable

Use clear, consistent source data and keep each formula’s ranges the same size. If you copy a formula to other locations, consider whether its references should move; dollar signs can lock a reference when appropriate. For instance, a fixed range written with dollar signs will remain fixed when the formula is copied, while a relative range adjusts. Do not lock references automatically, though: choose based on how the workbook is organized.

For a reusable report, you can put a category or threshold in a worksheet cell and reference it in the criterion. This allows someone to change the selection without editing formula text. If the workbook grows over time, an Excel Table can make data easier to maintain; structured references may also make formulas easier to understand. Regardless of layout, check that newly added records are included in the ranges used by the calculation.

Finally, treat the displayed average as a summary, not a complete description of the data. An average can be pulled upward or downward by unusually large or small values, and it does not show how many records qualified. When the count matters, pair the average with an appropriate count calculation or a clear note about the data being summarized.

Frequently Asked Questions

What is the difference between AVERAGEIF and AVERAGEIFS?
AVERAGEIF applies one condition. AVERAGEIFS can apply multiple conditions, all of which must be met for a row to qualify. Their argument order is different.

Can AVERAGEIF use more than one condition?
No. Use AVERAGEIFS when you need conditions such as a region and a date range to apply together.

Why does AVERAGEIF return #DIV/0!?
Usually, no rows meet the criterion, or the qualifying rows have no numeric cells in the average range. Check both the match and the values Excel is asked to average.

Does AVERAGEIF distinguish uppercase and lowercase text?
No. Text matching is not case-sensitive, so capitalization alone does not make otherwise identical criteria different.

Can I use a cell reference as the criterion?
Yes. For a comparison such as greater than a threshold in another cell, join the operator to the reference with &, as in the example above.

Does AVERAGEIF count zero as an average value?
Yes. Zero is numeric and participates in the average; a blank or text value in the average range does not contribute as a number.

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.