Excel AVERAGEIF #DIV/0! Error: Causes, Checks, and Fixes

Excel’s AVERAGEIF returns #DIV/0! when it has no numeric values to average among the cells that meet the criterion. Check whether any cells match, then check whether their corresponding average-range cells contain numbers; if you want a cleaner display, wrap the calculation in IFERROR or use a deliberate no-match result.

What the AVERAGEIF #DIV/0! error means

AVERAGEIF calculates the arithmetic mean of values associated with cells that satisfy a condition. A mean requires a sum divided by a count. If Excel finds no qualifying numeric values, the count is zero, so the calculation cannot produce an average and returns #DIV/0!. This is not necessarily evidence that your formula is misspelled or that the workbook is damaged. Often it is a valid result for the data currently present: the selected group has no numbers to average.

For example, a department may have no recorded sales yet, or a category criterion may not match the wording in the source data. A less obvious case is that rows do match the criterion, but their corresponding cells in the average range are blank or contain text. AVERAGEIF ignores blanks and text as numeric values, leaving nothing to average. A numeric zero is different: zero is a number and is included in the average.

How AVERAGEIF decides what to average

AVERAGEIF has the form AVERAGEIF(range, criteria, [average_range]). The first range is where Excel checks the condition. The criteria can be text, a number, an expression such as greater than a threshold, or a cell reference containing a criterion. The optional average_range supplies the values to average. If it is omitted, Excel averages qualifying numeric cells in the first range itself.

With departments in A2:A20, sales in B2:B20, and the department to summarize in E2, the formula is =AVERAGEIF(A2:A20,E2,B2:B20). It checks each department in column A and averages the corresponding sales values in column B when the department matches E2. If E2 contains East and the matching sales are 100, 150, and 200, the result is 150. If the matching sales cells are all blank or text, the result is #DIV/0!, even though East appears in column A.

The ranges should normally cover corresponding rows and have the same dimensions. If they do not, Excel may evaluate a shifted or unintended set of cells rather than the pairs you meant to compare. Using clearly aligned ranges, such as A2:A20 and B2:B20, makes the calculation easier to verify.

Check for missing matches and nonnumeric values

Start by confirming that the criterion actually appears in the criteria range. Look for extra spaces, spelling differences, inconsistent labels, or a criterion that points to the wrong cell. AVERAGEIF is not case-sensitive, so East and EAST match, but an extra trailing space can prevent a match. Also check whether a criterion is meant to be a number or text: a number stored as text may not behave as expected in numeric comparisons.

Next, inspect the corresponding cells in the average range. They may look numeric but be stored as text, contain a dash such as “—” to represent missing data, or be blank because a formula returns an empty string. A real date is stored as a number in Excel and can be averaged, although averaging dates may not be meaningful for your analysis. A date typed or imported as text is not a numeric date value.

For a simple match count, with department names in A2:A20 and the target department in E2, use =COUNTIF(A2:A20,E2). If the result is zero, the criterion found no matching labels. A positive count only proves that matching labels exist; it does not prove that the corresponding cells in B2:B20 contain numbers. Check those paired cells as well.

Confirm the criterion cell contains the intended value and has no accidental spaces.
Check that the criteria and average ranges begin and end on the intended rows.
Inspect matching average-range cells for numbers rather than blanks, text, or text-formatted numbers.
Check whether formulas in the average range return an empty string or an error.
Verify that zeros are legitimate data rather than placeholders for values that have not been entered.

Fix the formula without hiding useful information

If the underlying cause is an incorrect range or criterion, correct it rather than suppressing the error. If the data is incomplete by design, you can choose what the worksheet should show until numeric values are available. Returning a blank keeps a report tidy, while a message makes the missing average more visible.

With department names in A2:A20, numeric sales or other values in B2:B20, and the criterion in E2, this formula displays a message for any error raised by AVERAGEIF: =IFERROR(AVERAGEIF(A2:A20,E2,B2:B20),"No numeric values to average"). IFERROR catches #DIV/0!, but it also catches other errors, such as an error value encountered in the calculation. Use this approach when a general fallback is acceptable; it can otherwise conceal a problem that you would rather investigate.

If you want a blank instead, with the same ranges, use =IFERROR(AVERAGEIF(A2:A20,E2,B2:B20),""). The cell will appear empty when the calculation errors, but it still contains a formula. This distinction matters in later calculations and when someone audits the sheet. A blank fallback can also make missing data less obvious, so consider adding a note or a separate status indicator.

COUNTIF can distinguish “no matching department” from a calculation error when matched rows are expected to contain valid numeric data. With department names in A2:A20, values in B2:B20, and the target in E2, use =IF(COUNTIF(A2:A20,E2)=0,"No matching department",AVERAGEIF(A2:A20,E2,B2:B20)). This returns the message only when the label is absent. If labels match but the paired values are all blank or text, AVERAGEIF can still return #DIV/0!. Do not treat COUNTIF as a test for numeric averages.

For an exact text category, you can explicitly test whether at least one matching row has a numeric value. With categories in A2:A20, values in B2:B20, and the category in E2, use =IF(SUMPRODUCT(--(A2:A20=E2),--ISNUMBER(B2:B20))=0,"No matching numeric values",AVERAGEIF(A2:A20,E2,B2:B20)). This is a more targeted guard for that layout: it checks matching category cells and numeric paired values before returning the average. It uses direct equality for the category test, so it is not a general replacement for AVERAGEIF when your criterion uses wildcards or comparison operators.

Criteria that commonly lead to no average

AVERAGEIF criteria can include operators, and when an operator is combined with a cell reference, the operator is written inside quotation marks and joined to the reference with &. For example, with quantities in A2:A20, prices in B2:B20, and a minimum quantity in E2, use =AVERAGEIF(A2:A20,">="&E2,B2:B20). If no quantity reaches the threshold, the formula has no qualifying values and can return #DIV/0!. Confirm that E2 contains the intended numeric threshold.

For dates, the criterion must be built in a way Excel can compare to actual date values. With dates in A2:A20, amounts in B2:B20, and the first day of the target month in E2, a formula that averages amounts on or after that date is =AVERAGEIF(A2:A20,">="&E2,B2:B20). It does not by itself set an end date for the month; it includes every later date in the data. To average within a bounded date period, AVERAGEIFS can apply both a start and an end condition. The date cells must be real Excel dates for these comparisons to work reliably.

Wildcards can also affect text criteria: an asterisk matches a sequence of characters, and a question mark matches one character. That can be useful for grouping labels, but a misspelled or overly restrictive criterion may leave no matches. Criteria are generally not case-sensitive. If the desired grouping is more complicated than one condition, AVERAGEIFS may be more suitable, but it also returns #DIV/0! when no numeric values satisfy all its criteria.

AVERAGEIF versus AVERAGEIFS and IFERROR

Use AVERAGEIF for one condition, such as averaging sales for a single department. AVERAGEIFS is designed for multiple conditions, such as averaging sales for one department during a date period. It does not automatically solve the division-by-zero case: if the combined conditions select no numeric values, an error is still possible. The right function depends on how many conditions define the group, not on whether the data happens to be present.

IFERROR is a presentation choice, not a data repair. It can make a dashboard readable when “not available yet” is an expected outcome, but it should not disguise a wrong range, misspelled criterion, or unexpected source error. A useful practice is to show a clear message during data entry and switch to a blank only when the workbook’s users understand what a blank means.

Keep the worksheet easier to troubleshoot

Consistent data entry prevents many AVERAGEIF problems. Use one spelling for each category, keep numeric values in numeric cells, and avoid mixing explanatory text into a column intended for calculations. If a source system imports numbers as text, convert or clean the source data before relying on averages. Do not replace blanks with zero unless zero is the true value: including placeholder zeros changes the mean and can make an incomplete group look like it has low results.

When an average unexpectedly errors, temporarily test the criterion with COUNTIF and inspect a few matching rows. Then compare the source values and the formula’s range boundaries. This small audit is often more informative than immediately wrapping every formula in IFERROR. In a shared workbook, a message such as “No numeric values to average” can help users distinguish a legitimate empty group from a formula that needs attention.

Frequently Asked Questions

Why does AVERAGEIF return #DIV/0! when the category exists?
Because a matching category does not guarantee a numeric value in its corresponding average-range cells. Those cells may be blank, contain text, or return an empty string from a formula. Check the paired cells, not just the category column.

Does AVERAGEIF include zero values?
Yes. A numeric zero is included in the average and affects its result. Blank cells and text are not treated as numeric values. If zero is only a placeholder for missing information, clean or exclude it according to the meaning of your data.

Will IFERROR fix the underlying problem?
No. IFERROR replaces an error with a value you choose; it does not create missing data or correct a faulty criterion. Use it when a no-result outcome is expected, and investigate the formula or source data when the error is unexpected.

Why does AVERAGEIFS also show #DIV/0!?
AVERAGEIFS has no numeric values to average when no rows satisfy all its conditions, or when the qualifying average-range cells contain no numbers. Review each criterion and the corresponding average-range values.

Can I show a blank instead of #DIV/0!?
Yes. Wrap the AVERAGEIF calculation in IFERROR and use an empty text string as the fallback. Remember that the result is a formula cell that displays blank, not a truly empty cell, and that IFERROR can also hide errors other than #DIV/0!.

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.