Excel AVERAGEIF Not Working? Fix Common Errors

If Excel AVERAGEIF is not working, first check that the criteria range, criterion, and average range are in the right order and that the cells to average contain numbers, not numbers stored as text. The function is AVERAGEIF(range, criteria, [average_range]); a wrong match, a blank result, or an error usually comes from the data or criteria rather than from the function being unavailable.

What AVERAGEIF does

AVERAGEIF calculates the average of values that meet one condition. It checks each cell in a criteria range, selects the corresponding value in an average range when the condition is true, and averages the selected numeric values. For example, you can use it to average sales for one region, hours logged to one project, or scores above a threshold.

With regions in A2:A6 and sales in B2:B6, where the East rows have sales of 120, 0, and 180, the following formula returns 100. The zero is included in the average: (120 + 0 + 180) / 3 = 100.

With regions in A2:A6 and the corresponding sales in B2:B6, use: =AVERAGEIF(A2:A6,"East",B2:B6)

The third argument is optional. If you leave it out, Excel averages the numeric cells in the criteria range itself. That is useful when the cells being tested are also the values you want to average, but it is a common source of confusion when labels and numbers are in separate columns.

Check the argument order and range alignment

The first argument is the cells Excel should test, the second is the condition, and the optional third argument is the cells to average. Reversing the first and third arguments may produce a result that looks plausible but answers a different question. Include the average range whenever the criterion and the values to average are in different columns.

With departments in A2:A20, amounts in B2:B20, and the word East in D2, this formula averages amounts for rows whose department matches D2:

With department labels in A2:A20, amounts in B2:B20, and the criterion in D2, use: =AVERAGEIF(A2:A20,D2,B2:B20)

Keep the criteria and average ranges the same height and aligned row by row. If the criteria range starts at row 2 but the average range starts at row 3, Excel may pair a department with the wrong amount. Use matching endpoints such as A2:A20 and B2:B20, and check that copied formulas have not shifted one range while leaving the other fixed.

In a formula copied down a table, consider whether references should move. For example, dollar signs lock a range so it stays fixed when copied, while a reference such as A2 can change to A3. Press F4 while editing a reference in Excel for Windows to cycle through reference styles; on Mac, the shortcut depends on the keyboard and system settings, and you can type the dollar signs directly.

Make sure the criterion matches the data

Text criteria must match the cell contents. “East” and “east” are treated as equivalent because AVERAGEIF is not case-sensitive, but “East ” with a trailing space may not match “East.” Extra spaces, inconsistent spelling, punctuation, and nonprinting characters can all prevent the expected rows from being included. Compare a problem cell with the criterion, or clean the source data if entries are inconsistent.

For a condition involving a comparison operator, put the operator and value together inside the criterion text. With scores in A2:A10, the following formula averages the scores that are at least 100. If the qualifying values are 100, 120, and 150, the result is 123.33 (rounded to two decimal places).

With the scores to test and average in A2:A10, use: =AVERAGEIF(A2:A10,">=100")

If the comparison value is in a cell, join the operator to that cell with an ampersand. For example, with scores in A2:A10 and the threshold in D1, use =AVERAGEIF(A2:A10,">="&D1). The criterion is built from the text operator and the value in D1; typing ">=D1" instead would search for that literal text, not use the value in D1.

For text patterns, AVERAGEIF accepts wildcards: an asterisk matches any number of characters, a question mark matches one character, and a tilde escapes a wildcard when you need to search for it literally. With product names in A2:A20 and prices in B2:B20, the criterion "*North*" can match names containing “North” anywhere. Wildcards are for text matching, not a substitute for numeric comparisons.

Check for numbers stored as text, blanks, and zeros

Cells that look like numbers may actually contain text, especially after importing a CSV file, copying data from a website, or using a leading apostrophe. Text in the average range is ignored, so a formula may average fewer values than expected or return #DIV/0! if no qualifying numeric values remain. Select a sample cell and check whether Excel recognizes it as a number; changing its number format alone does not convert text into a numeric value.

To convert imported values, select the cells and look for Excel’s warning icon and Convert to Number option when available. You can also use Data > Text to Columns and finish the wizard to convert suitable text values, or re-import the source with the correct data type. Check that conversion is appropriate before applying it, since text such as product codes may be intended to remain text.

AVERAGEIF ignores blank cells and text in the average range, but it includes numeric zeros. A zero is not the same as a blank: including zeros can lower the result, while excluding them changes the question being answered. If zero values should not count, use a second condition rather than deleting or hiding data. AVERAGEIFS supports multiple criteria; with regions in A2:A10 and amounts in B2:B10, this averages East amounts greater than zero:

With regions in A2:A10 and numeric amounts in B2:B10, use: =AVERAGEIFS(B2:B10,A2:A10,"East",B2:B10,">0")

For this formula, suppose the East amounts are 120, 80, 150, and 0. Only 120 and 150 qualify, so the result is 135. AVERAGEIFS is available in current Microsoft 365 Excel and is useful when one condition is not enough.

Understand common errors and unexpected results

#DIV/0!: No qualifying numeric values were available to average. The criterion may match no rows, or the matching average cells may be blank or text. Check both the condition and the values being averaged.
A result that is too high or low: Confirm that zeros are intended to count, that the average range points to the correct column, and that its rows line up with the criteria range.
A result of 0: The matching numeric values may genuinely be zero, or cells that look numeric may contain text. Check the underlying values and the rows that meet the condition.
#VALUE! or another error: Inspect the referenced cells for errors and confirm that the formula uses valid ranges and separators for your Excel settings. A cell error in data used by a calculation can affect the result.

When you cannot tell which rows Excel is matching, apply a filter to the criteria column and inspect the corresponding average cells. This is often more informative than repeatedly editing the formula. You can also temporarily use a simple criterion, such as a label you can verify by sight, to separate a matching problem from a data-type problem.

Use dates and cell-based criteria correctly

Excel stores real dates as serial numbers, so date comparisons work when the cells contain genuine Excel dates. Dates imported as text may look identical on screen but fail to compare as expected. Check that the source dates are recognized as dates before troubleshooting the formula.

With dates in A2:A5 and amounts in B2:B5, suppose the dates are January 1, 2, 3, and 4, 2026, and the amounts are 10, 20, 30, and 40. This formula averages amounts from January 3 onward; it returns 35.

With dates in A2:A5 and their amounts in B2:B5, use: =AVERAGEIF(A2:A5,">="&DATE(2026,1,3),B2:B5)

DATE creates an actual date value, while joining it to the comparison operator forms the criterion. This is generally safer than typing a date directly into a formula, because date display and interpretation can vary with regional settings. If your data includes times as well as dates, a comparison for a date may also include or exclude times depending on the exact boundary; check whether the source values contain time portions.

Check calculation settings and regional separators

If the formula is correct but its displayed result does not update after editing source data, check calculation mode. In Excel for Microsoft 365 on Windows, go to Formulas > Calculation Options and select Automatic. Manual calculation can leave formula results unchanged until Excel recalculates. On Mac, use the Formulas tab and its Calculation Options control where available; the precise ribbon layout can vary by version.

Excel formulas use the list separator configured for the system’s regional settings. The examples here use commas, as in =AVERAGEIF(A2:A10,"East",B2:B10). If Excel reports a problem with the formula syntax, your installation may require semicolons instead. Use the separator Excel accepts in your environment; do not replace commas in decimal values or other parts of a formula indiscriminately.

When to use AVERAGEIFS or another approach

Use AVERAGEIF for one condition and AVERAGEIFS when several conditions must all be met. AVERAGEIFS puts the average range first, followed by pairs of criteria ranges and criteria, so its argument order differs from AVERAGEIF. For example, averaging East sales of at least 100 requires the region condition and the amount threshold.

With regions in A2:A10 and sales in B2:B10, use: =AVERAGEIFS(B2:B10,A2:A10,"East",B2:B10,">=100")

If the only problem is that you need to show the qualifying rows as well as their average, use a filter to inspect them or consider a PivotTable for a repeatable summary across categories. AVERAGEIF is usually the simplest choice for a single condition in a formula; it is not necessary to replace it just because a workbook contains many rows.

Frequently Asked Questions

Why does AVERAGEIF return #DIV/0!?
It means there are no qualifying numeric values to average. Check whether the criterion matches any cells and whether the corresponding average-range cells contain numbers rather than blanks or text.

Does AVERAGEIF count zero values?
Yes. Numeric zeros in the average range count in the average. Blank cells and text are ignored, so do not treat a blank and a zero as interchangeable.

Can AVERAGEIF use more than one condition?
No. AVERAGEIF handles one condition. Use AVERAGEIFS when you need multiple conditions, such as a particular region and a minimum sales amount.

Why does my date condition not match anything?
The dates may be stored as text, or the criterion may be interpreted differently under your regional settings. Confirm that the cells contain real Excel dates and consider building the condition with DATE, as shown above.

Why does Excel reject my formula even though it looks correct?
Your Excel list separator may differ from the comma used in the examples. Check whether your installation expects semicolons, and confirm that quotation marks are straight double quotes and that every parenthesis is paired.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.