Fixing Excel AVERAGEIFS That Returns #DIV/0! When Matching Rows Exist

You've written an AVERAGEIFS formula, you can see the matching rows right there in the spreadsheet, and yet Excel insists on showing #DIV/0!. The error means Excel found zero rows that matched all your criteria — which clearly isn't true. Something in your criteria or your data is preventing the match.

This guide walks through every common cause of this mismatch, shows you how to spot each one, and gives you a concrete fix for each scenario.

What You'll Learn

• Why AVERAGEIFS returns #DIV/0! even when matching data is visible
• How trailing spaces, type mismatches, and date format differences silently break criteria matching
• How to quickly diagnose which cause is affecting your formula
• Reliable fixes you can apply without rebuilding your entire sheet

Why AVERAGEIFS Returns #DIV/0! in the First Place

AVERAGEIFS divides the sum of qualifying values by the count of rows that meet all criteria. When that count is zero, you get a division-by-zero error. Excel isn't lying — from its perspective, not a single row satisfies every condition simultaneously.

The frustrating part is that the failure is almost always invisible. Your eyes see "East" in column A and "East" in your criteria cell, but Excel sees something slightly different in at least one of them. The fixes are straightforward once you know what to look for.

Cause 1: Trailing Spaces in Your Criteria or Data

This is the single most common culprit. A cell that displays East might actually contain East (with a trailing space), and Excel's string comparison is exact. One extra character means no match.

Trailing spaces usually arrive when data is pasted from another system, exported from a database, or typed by a user who hit the spacebar at the end of an entry. You won't see them unless you click into the cell and look at the formula bar.

How to spot it

Use LEN to compare what you expect against what's actually there:

=LEN(A2)          ' Expected: 4 for "East"
=LEN("East")      ' Returns: 4

If LEN(A2) returns 5 instead of 4, there's a hidden space. You can also use =TRIM(A2)=A2 — if this returns FALSE, the cell has leading or trailing spaces.

The fix

Wrap your criteria range in TRIM inside a helper column, or wrap the criteria value itself:

=AVERAGEIFS(C2:C100, TRIM(A2:A100), "East", B2:B100, "Q1")

Note: TRIM inside a criteria range only works if you enter the formula as an array formula (Ctrl+Shift+Enter in older Excel, or wrap in IFERROR + helper columns). The cleaner long-term fix is to clean the source data with TRIM once and paste it back as values. This is the same class of problem discussed in fixing broken VLOOKUP results caused by trailing spaces.

Cause 2: Numbers Stored as Text

If your average range or a numeric criteria column contains numbers that Excel is treating as text, the criteria comparison will fail. This happens constantly with data imported from CSV files, external APIs, or copied from web pages.

A dead giveaway: numbers are left-aligned in their cells instead of right-aligned (Excel's default for actual numbers). You might also see the small green triangle in the upper-left corner of the cell.

How to spot it

Use ISNUMBER to test a suspect cell:

=ISNUMBER(B2)   ' Returns FALSE if the number is stored as text

Alternatively, try adding zero to the cell: =B2+0. If it returns an error, it's text.

The fix

Select the affected column, open Data → Text to Columns, click through the wizard without changing anything, and finish. Excel re-evaluates the column and converts text-numbers to real numbers. You can also use VALUE() in a helper column:

=VALUE(B2)

Then use that helper column as your criteria range instead of the original. If your criteria itself is a number, make sure your formula uses a number literal, not a quoted string: use 500 not "500".

Cause 3: Date Criteria That Don't Match the Cell Format

Dates in Excel are integers under the hood, but how they're stored and displayed varies. A cell showing 01/15/2024 might be a real Excel date serial, a text string, or a date formatted differently than your criteria.

If you type a date directly into your AVERAGEIFS criteria argument, Excel may interpret it as text rather than a date, so no rows match. This is the exact same trap described in fixing Excel COUNTIFS that returns zero with date range criteria.

The fix

Use DATEVALUE to force Excel to treat your string as a date, or reference a cell that already holds a proper date:

' Wrong — the string might not match the date serial
=AVERAGEIFS(C2:C100, A2:A100, "1/15/2024")

' Right — DATEVALUE converts the string to a date serial
=AVERAGEIFS(C2:C100, A2:A100, DATEVALUE("1/15/2024"))

' Best — reference a cell containing the date
=AVERAGEIFS(C2:C100, A2:A100, E2)

Also check that the date column isn't actually storing text that looks like dates. Use =ISNUMBER(A2) — a real date returns TRUE because it's stored as a number internally.

Cause 4: Criteria Range and Average Range Are Different Sizes

AVERAGEIFS requires that every range argument — the average range and all criteria ranges — covers exactly the same number of rows and columns. If they don't match, Excel either throws an error or silently finds zero matches.

This typically happens after you insert or delete rows and forget to update one of the range references in the formula.

How to spot it

Click on your formula and examine each range argument. Count (or note) the row numbers for each:

=AVERAGEIFS(C2:C100, A2:A99, "East", B2:B100, "Q1")
'                             ^^^^^^
' C and B go to row 100; A only goes to row 99 — mismatch!

The fix

Make all ranges identical in size. The easiest way is to use full-column references when your data has no header conflicts:

=AVERAGEIFS(C:C, A:A, "East", B:B, "Q1")

Full-column references also mean you never have to update the formula when new rows are added.

Cause 5: Wildcard Characters Used Incorrectly

AVERAGEIFS supports the * (any sequence of characters) and ? (any single character) wildcards — but only for text criteria. If you use a wildcard with a numeric criteria range, it won't match anything.

A subtler version of this problem: you want an exact match for a string that contains an asterisk or question mark (like a product code). Excel will treat those as wildcards and match the wrong rows.

The fix for literal asterisks or question marks

Escape the wildcard character with a tilde (~):

' Matches cells containing the literal string "Q1*"
=AVERAGEIFS(C2:C100, A2:A100, "Q1~*")

The fix for partial matching

Wrap your search term with asterisks to match any cell containing the text:

' Matches "East Region", "Far East", "Northeast", etc.
=AVERAGEIFS(C2:C100, A2:A100, "*East*")

Cause 6: Logical Operators Written the Wrong Way

When you want to use a comparison like "greater than 100" as criteria, the operator and value must be combined into a single quoted string. Developers sometimes write them separately or without quotes, which silently fails.

Common mistakes

' Wrong — operator outside quotes
=AVERAGEIFS(C2:C100, B2:B100, >100)

' Wrong — two separate arguments
=AVERAGEIFS(C2:C100, B2:B100, ">", 100)

' Right — operator and value together inside quotes
=AVERAGEIFS(C2:C100, B2:B100, ">100")

Combining a cell reference with an operator

If the threshold lives in a cell (say, E2), you need to concatenate the operator string with the cell reference:

=AVERAGEIFS(C2:C100, B2:B100, ">"&E2)

The & joins the ">" string to the value in E2. This is one of those things that looks odd the first time but becomes second nature quickly.

How to Diagnose the Root Cause Fast

Rather than guessing which cause applies, use COUNTIFS with the same arguments first. COUNTIFS tells you exactly how many rows match your criteria without needing to average anything — and it makes the zero-match problem concrete:

=COUNTIFS(A2:A100, "East", B2:B100, "Q1")

If this returns zero, the matching problem is confirmed. Now strip your criteria down one condition at a time to isolate the failing criterion:

' Test condition 1 alone
=COUNTIFS(A2:A100, "East")

' Test condition 2 alone
=COUNTIFS(B2:B100, "Q1")

The one that returns zero is your culprit. Then apply the relevant fix from the sections above. This same step-by-step approach is useful for debugging SUMIF formulas that return zero — the diagnosis process is nearly identical.

You can also use EXACT to test whether two cells are truly identical (case-sensitive, no hidden characters):

=EXACT(A2, "East")   ' Returns TRUE only if the cell is exactly "East"

For cases where you suspect number-as-text issues across a whole column, a quick way to verify is checking for misalignment. Select the column and look at the alignment — if numbers sit on the left side, they're stored as text.

If your data comes from pivot tables or external queries, it's worth checking how Excel handles blank values in pivot table outputs, since those blanks can show up in your data ranges and break criteria matching in unexpected ways.

Common Pitfalls to Watch For

• Case sensitivity: AVERAGEIFS criteria matching is case-insensitive for text, so "east" and "East" match the same rows. Case is not your problem — hidden characters are.
• Empty criteria string: Using "" as a criterion matches only truly empty cells, not cells with spaces. If your data has cells containing a single space, they won't match "".
• Multiple criteria are AND, not OR: AVERAGEIFS only returns rows where every criterion is met simultaneously. If you need OR logic, you'll need to use multiple AVERAGEIFS calls combined with IFERROR, or restructure with SUMPRODUCT.
• IFERROR masking real problems: Wrapping in IFERROR(..., 0) hides the error but doesn't fix the root cause. Use it only as a last resort for display purposes, and only after you understand why the error occurs.
• Range not updating after sort: If you sort your data after writing the formula, absolute references stay correct but named ranges based on position can shift. Prefer table references (Table1[Region]) for self-adjusting ranges.

If you've inherited a complex workbook with multiple linked formulas, Excel INDEX MATCH returning wrong rows on unsorted data is another common trap that often appears alongside AVERAGEIFS issues in the same analysis sheets.

Next Steps

Here's what to do right now to get your AVERAGEIFS working:

1. Run =COUNTIFS(...) with your exact same criteria to confirm the row count is zero. This turns a vague error into a measurable fact.
2. Strip criteria down one at a time to find which condition is failing.
3. Run =LEN() and =ISNUMBER() on a few cells in each criteria column to rule out trailing spaces and text-stored numbers.
4. If dates are involved, switch your criteria to reference a proper date cell or wrap in DATEVALUE.
5. Once fixed, convert your ranges to Excel Tables so future rows auto-expand without breaking your formula references.