Fixing Excel AVERAGEIFS That Returns Zero When Criteria Exclude All Matching Rows

You build a report, filter by region and product, and AVERAGEIFS hands you a clean zero. No error flag, no warning — just a number that looks valid but is completely wrong. When no rows match your criteria, AVERAGEIFS does not return a blank or an error; it returns zero, and that zero will silently corrupt any downstream calculation that treats it as a real average.

What's actually happening when AVERAGEIFS returns zero

Before diving into the fixes, it helps to know exactly what Excel is doing under the hood. AVERAGEIFS is designed to average values across rows where every specified criterion is satisfied. When zero rows satisfy those criteria, the function has no values to average — but instead of surfacing that as a visible failure, Excel returns 0 in most versions and a #DIV/0! error in others depending on context.

The inconsistency makes this bug especially dangerous: on one machine or Excel version you see zero, on another you see an error. Either way, the behavior is not what you want in a production report.

What you'll learn

• Why AVERAGEIFS returns zero when criteria match no rows
• How to use IFERROR to replace that zero with a blank or meaningful fallback
• How to gate the calculation with IF and COUNTIFS so the formula only runs when data exists
• How to build an array formula alternative that gives you more control
• How to pick the right fix for your specific spreadsheet structure

Prerequisites

• Excel 2016 or later (most fixes work in Excel 2010+; array formula syntax differs in older versions)
• A basic understanding of how criteria ranges and criteria work in AVERAGEIFS
• Familiarity with entering Ctrl+Shift+Enter formulas if you're on Excel 2019 or earlier

How AVERAGEIFS evaluates criteria ranges

The syntax for AVERAGEIFS is =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). Excel walks each row, checks whether every criterion is satisfied simultaneously, and collects the corresponding values from average_range. Those collected values are then summed and divided by their count.

The division step is where the problem lives. If the collected set is empty — because no row passed every filter — Excel is dividing by zero. Some versions surface #DIV/0!; others silently return 0. Neither is the blank cell or meaningful label your report needs.

Why zero is the wrong signal here

Zero is a legitimate business value in many datasets. An average revenue of $0 could mean a real period with no sales. If AVERAGEIFS also returns 0 when no data matches, you lose the ability to distinguish "no sales" from "no matching rows" — and every chart, pivot table, or summary formula that reads that cell inherits the ambiguity.

This is the same category of silent failure covered in articles like fixing COUNTIFS returning zero when criteria range sizes differ — the formula looks healthy, the number looks plausible, but the underlying cause is invisible.

Fix 1: Wrap AVERAGEIFS in IFERROR to return a blank or fallback

The fastest one-liner fix is to catch the #DIV/0! error (or the misleading zero in versions that suppress it) with IFERROR. This works reliably across Excel versions and is easy for anyone maintaining the file to understand.

=IFERROR(AVERAGEIFS(C2:C100, A2:A100, F1, B2:B100, F2), "")

The empty string "" leaves the cell visually blank. If you prefer a label, use "No data" instead. If a downstream formula needs a number (not text), use NA() so dependent charts gap rather than plot zero.

=IFERROR(AVERAGEIFS(C2:C100, A2:A100, F1, B2:B100, F2), NA())

Limitation: IFERROR catches all errors, including ones caused by misconfigured ranges or typos. If you want to handle only the "no match" scenario while letting other errors surface, use Fix 3 instead.

Fix 2: Use IF to gate the calculation before it runs

A tighter approach wraps the AVERAGEIFS call in an IF that checks for matching rows before attempting the average. This makes the intent explicit in the formula itself.

=IF(COUNTIFS(A2:A100, F1, B2:B100, F2) > 0,
    AVERAGEIFS(C2:C100, A2:A100, F1, B2:B100, F2),
    "")

Here COUNTIFS checks whether at least one row satisfies both criteria. If it does, AVERAGEIFS runs. If not, the cell stays blank. The criteria arguments in both functions must be identical — a common mistake is updating one and forgetting the other.

This pattern also makes auditing easier. A colleague reading the formula immediately sees the guard condition without needing to know that IFERROR is masking a specific error type.

Fix 3: Test for matching rows first with COUNTIFS

Fix 2 already incorporates COUNTIFS, but it's worth understanding COUNTIFS as a standalone diagnostic tool. Before you change anything, add a helper cell:

=COUNTIFS(A2:A100, F1, B2:B100, F2)

If this returns zero, your criteria are excluding every row. Common reasons include:

• A trailing space in F1 or F2 that doesn't visually appear but breaks the match
• A number stored as text in one column but as a true number in the other (see fixing MATCH returning #N/A for numbers stored as text for a detailed breakdown of this issue)
• Case-sensitive data where your criteria use different capitalisation (AVERAGEIFS is case-insensitive, so this is rarely the culprit, but worth ruling out)
• Date values that look identical but were entered in different formats

Once COUNTIFS confirms zero matches, you know the problem is criteria logic, not a formula bug. Fix the source data or the criteria values, and the original AVERAGEIFS may start working correctly on its own.

Fix 4: Replace AVERAGEIFS with an array formula for full control

When you need finer control — for example, averaging only positive values across multiple conditions, or averaging with a weighted denominator — an array formula gives you that flexibility.

In Excel 365 / Excel 2021 (dynamic arrays):

=LET(
  matched, (A2:A100=F1) * (B2:B100=F2),
  vals,    IF(matched, C2:C100, ""),
  count,   SUM(matched),
  IF(count=0, "", SUM(matched * C2:C100) / count)
)

The LET function makes each step readable. matched is a boolean array that equals 1 where both criteria are met. The final IF guards against division by zero explicitly.

In Excel 2019 and earlier (Ctrl+Shift+Enter required):

=IF(SUM((A2:A100=F1)*(B2:B100=F2))=0, "",
  SUMPRODUCT((A2:A100=F1)*(B2:B100=F2)*C2:C100) /
  SUMPRODUCT((A2:A100=F1)*(B2:B100=F2)))

Enter this with Ctrl+Shift+Enter to make it an array formula. Excel will wrap it in curly braces: {=IF(...)}. The SUMPRODUCT version works without Ctrl+Shift+Enter in Excel 2010+ because SUMPRODUCT natively handles arrays.

Choosing the right fix for your situation

For most everyday reports, Fix 1 or Fix 2 is sufficient. Reach for Fix 4 only when you genuinely need logic that AVERAGEIFS can't express.

Common pitfalls when fixing AVERAGEIFS

Mismatched range sizes. Every criteria range and the average range must cover the same number of rows. If C2:C100 is 99 rows but A2:A99 is 98 rows, Excel raises an error. This is a separate failure mode from the zero-return issue, but it often appears at the same time during debugging.

Criteria that use cell references vs. hardcoded values. When you write AVERAGEIFS(C2:C100, A2:A100, "North"), the string "North" will never accidentally match a number. But when you reference F1, whatever data type is in F1 matters. A cell formatted as text holding the number 2024 does not match a true numeric 2024 in column A.

Wildcard characters in criteria. AVERAGEIFS supports * and ? wildcards for text criteria. If your criteria string accidentally contains an asterisk — for example, because it was copied from another system — it will match everything or nothing unexpectedly. Escape literal asterisks with a tilde: "~*".

IFERROR hiding real bugs. If you paste Fix 1 into a formula that has a genuine range mismatch, IFERROR will suppress the error and return a blank, making the underlying bug invisible. Always confirm COUNTIFS returns a positive number before deciding the issue is purely "no matching data."

Silent data issues like these are common across tools. If you work in Python as well, the same kind of unexpected zero appears in Pandas when NaN values are coerced to zero during type conversion — worth knowing if your Excel data originates from a Python pipeline.

Wrapping up

AVERAGEIFS returning zero when no rows match is a predictable failure once you understand the division-by-zero mechanics underneath it. Here are the concrete steps to take right now:

1. Add a COUNTIFS helper cell with identical criteria to confirm whether the problem is truly "no matches" or a range configuration error.
2. If criteria genuinely match nothing, check for trailing spaces, type mismatches, and date format inconsistencies in your source data before changing the formula.
3. Apply Fix 1 (IFERROR wrapper) for the fastest safe patch, or Fix 2 (IF + COUNTIFS gate) if you want the logic self-documented in the formula.
4. Switch to the LET or SUMPRODUCT array formula (Fix 4) only when you need conditional logic beyond what AVERAGEIFS supports natively.
5. After fixing, test with a criteria combination that intentionally matches nothing to confirm the cell shows a blank or your chosen fallback rather than zero.

Getting these edge cases right is what separates a spreadsheet that works on clean data from one that holds up in production. If you're also working across multiple XLOOKUP lookups in the same workbook, it's worth checking how approximate match mode in XLOOKUP can silently return wrong results — a similar class of quiet failure that deserves the same careful treatment.