Excel AVERAGEIF Formula Examples: Criteria, Ranges, Fixes

Use Excel’s AVERAGEIF function to average values that meet one condition: =AVERAGEIF(A2:A6,"North",B2:B6). If A2:A6 contains regions and B2:B6 contains sales, this formula averages sales for North; the examples below show how to adapt it for numbers, text, dates, and common criteria.

AVERAGEIF syntax and how it works

AVERAGEIF checks cells in a range against a criterion, then averages the corresponding numeric cells in an optional average range. Its syntax is =AVERAGEIF(range,criteria,[average_range]). The square brackets indicate that average_range is optional; do not type the brackets into a formula.

range is the cells Excel checks against your condition.
criteria is the condition, such as a text label, a number, or an expression written in quotes.
average_range contains the numbers to average. If you omit it, Excel averages qualifying numeric cells in range itself.

When you use separate ranges, keep them the same size and aligned row by row. For example, A2:A6 can hold the conditions and B2:B6 the values to average. A match in A2 selects the corresponding value in B2, a match in A3 selects B3, and so on. AVERAGEIF accepts one condition; it does not add an additional criterion just because the worksheet has another column.

Example: average values for an exact text match

Suppose A2:A6 contains North, South, North, East, and North, while B2:B6 contains 120, 80, 160, 100, and 140. To calculate the average sales for North, use this formula:

With region names in A2:A6 and sales in B2:B6:

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

The result is 140: Excel selects 120, 160, and 140, and (120 + 160 + 140) ÷ 3 = 140. Text criteria go in double quotation marks when typed directly into a formula. Text matching is not case-sensitive, so “North” also matches “NORTH.” However, an exact text criterion does not automatically match a longer label such as “Northwest.”

If the condition is in a worksheet cell, refer to that cell instead of typing the label into the formula. This is useful when someone may change the selection without editing the formula.

With region names in A2:A6, sales in B2:B6, and the chosen region in D2 (North):

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

This also returns 140. If D2 is changed to South, the result becomes 80, because there is one South row in this sample.

Example: average numbers above or below a threshold

Criteria can compare numbers using operators such as greater than (>), less than (<), greater than or equal to (>=), and not equal to (<>). Put the operator and number together inside quotation marks. For the sample sales values 120, 80, 160, 100, and 140, this formula averages entries of at least 100:

With sales in B2:B6:

=AVERAGEIF(B2:B6,">=100")

The result is 130: the qualifying values are 120, 160, 100, and 140, whose total is 520; 520 ÷ 4 = 130. Because the formula omits average_range, Excel checks and averages the same cells. The value 80 does not meet the condition.

You can store the threshold in a cell and join it to the operator with the ampersand (&). This makes the condition easier to adjust. If D2 contains 100:

With sales in B2:B6 and the threshold in D2:

=AVERAGEIF(B2:B6,">="&D2)

The result is also 130 when D2 is 100. The operator stays inside quotation marks, while the cell reference is outside them. Typing ">=D2" would instead ask Excel to look for that text, not compare values with the number in D2.

Example: find text that contains a word

For text conditions, AVERAGEIF supports wildcards. An asterisk (*) represents any sequence of characters, while a question mark (?) represents one character. Use a wildcard when you want to match part of a label rather than the whole cell. For example, assume A2:A6 contains Office, Office supplies, office, Travel, and Office; B2:B6 contains 20, 40, 60, 80, and 100.

With category labels in A2:A6 and amounts in B2:B6:

=AVERAGEIF(A2:A6,"*Office*",B2:B6)

The result is 55. The criterion matches the first, second, third, and fifth labels; their amounts total 220, and 220 ÷ 4 = 55. The match is case-insensitive. Without the asterisks, the criterion “Office” matches the exact label, not “Office supplies”; the exact-match average is 60, from 20, 60, and 100.

A question mark can help with a fixed-length variation. For instance, “?at” matches a three-character entry ending in “at,” such as “cat” or “hat.” To search for a literal asterisk or question mark in cell text, precede that wildcard with a tilde (~). This distinction matters when the symbol itself is part of a product name or code.

Example: average values within a date period

Excel stores real dates as numbers, so date criteria can use comparison operators. For example, the criterion below selects dates earlier than January 1, 2026. Using a boundary like the first day of the next year is a reliable way to include every date in 2025, including dates that may contain a time component.

Assume A2:A5 contains the actual Excel dates January 10, 2025; February 10, 2025; January 10, 2026; and February 10, 2026, while B2:B5 contains 10, 20, 30, and 40:

=AVERAGEIF(A2:A5,"<"&DATE(2026,1,1),B2:B5)

The result is 15: the two dates before 2026 correspond to 10 and 20, and (10 + 20) ÷ 2 = 15. DATE(year,month,day) constructs a date value, and the ampersand joins that value to the less-than operator. If dates are stored as text rather than recognized by Excel as dates, comparisons may not work as expected; convert them to real dates before relying on date-based calculations.

Build criteria from cells and manage ranges

Cell references make formulas reusable. You can change a text label, threshold, or date in a designated input cell without rewriting the formula. For a text criterion, refer to the criterion cell directly. For a comparison, join the operator to the cell reference, as in the threshold example above. When a formula will be copied, consider whether references should move with the copied formula or remain fixed: dollar signs make a reference absolute, as in $D$2.

For example, if a summary table has criteria in D2:D4 and you copy a formula down, the condition reference should usually change from D2 to D3 and D4. The data ranges, however, should stay fixed. With source regions in A2:A6, source sales in B2:B6, and the first criterion in D2:

=AVERAGEIF($A$2:$A$6,D2,$B$2:$B$6)

For D2 set to North, the result is 140. The dollar signs lock the source ranges when copying the formula down, while D2 remains relative so it can update to D3. This is a practical layout for a small summary report. If the source data will grow regularly, an Excel Table can make ranges expand as rows are added; structured references are another option, but the basic AVERAGEIF logic remains the same.

Common AVERAGEIF problems and how to fix them

A formula that returns an unexpected result often has a mismatch between the criterion and the worksheet data. Check the following before changing the calculation:

#DIV/0!: No cells met the condition, or the matching average-range cells contain no numbers. Confirm the criterion and ensure at least one matching row has a numeric value.
Numbers stored as text: Values that look like numbers may not be included in an average. Convert them to numeric values, and check for leading apostrophes or imported text.
Extra spaces or inconsistent labels: “North” and “North ” may not behave like identical entries. Clean the source text if labels came from manual entry or an export.
Incorrect criteria construction: For a cell-based threshold, combine the operator and cell with &. Keep direct text or comparison expressions in quotation marks.
Misaligned ranges: Use corresponding, same-sized ranges so each qualifying condition maps to the intended value on that row.
Blank and zero confusion: A blank is not the same as zero. AVERAGEIF ignores blank cells in the average range, while a numeric zero is included in the calculation.

AVERAGEIF returns an error rather than a meaningful average when no numeric values qualify. You can handle that situation with IFERROR if a report needs a friendlier display, but decide what the fallback should mean: a blank, a message, or a deliberate zero. Zero can be misleading because it looks like a measured average rather than “no qualifying data.”

AVERAGEIF vs. AVERAGEIFS and other options

Use AVERAGEIF when one condition is enough, such as “average sales for North” or “average values at least 100.” When the calculation needs two or more conditions, use AVERAGEIFS. Its argument order differs: the average range comes first, followed by each criteria range and criterion pair.

Suppose A2:A6 contains regions North, South, North, North, East; B2:B6 contains Hardware, Hardware, Software, Hardware, Hardware; and C2:C6 contains 120, 80, 160, 140, and 100. To average sales only where the region is North and the category is Hardware:

With regions in A2:A6, categories in B2:B6, and sales in C2:C6:

=AVERAGEIFS(C2:C6,A2:A6,"North",B2:B6,"Hardware")

The result is 130: the matching sales are 120 and 140, and their average is 130. AVERAGEIF cannot express both conditions in one call. For a simple overall average with no condition, use AVERAGE instead. If the goal is to display qualifying rows rather than calculate one average, filtering the data may be clearer than using an average function.

Frequently Asked Questions

Can AVERAGEIF use more than one condition?
Not in a single AVERAGEIF formula. Use AVERAGEIFS when you need multiple conditions, such as a region and a product category.

Does AVERAGEIF distinguish uppercase and lowercase text?
No. Its text criteria are case-insensitive. “North” and “NORTH” match the same text for this function. Use a different approach if a case-sensitive comparison is essential.

What does AVERAGEIF return when nothing matches?
It returns #DIV/0! when there are no qualifying numeric values to average. Check the condition and source data, or use error handling if the workbook should display a chosen message instead.

Can the criterion be a date in a cell?
Yes. Reference a cell containing a real Excel date. For a condition such as dates before that cell’s date, join the operator to the reference with &, for example "<"&D2 as the criteria argument.

Why is a matching row not included in the average?
Check that the condition actually matches the cell, that the corresponding average-range entry is numeric, and that the ranges line up. A blank or text entry in the average range is not treated as a numeric value to average.

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.