Fixing Excel AVERAGEIFS #DIV/0! Error When Criteria Reference a Named Range

You've built a clean AVERAGEIFS formula, defined a named range for the criteria, and Excel throws back a #DIV/0! error. The named range resolves correctly when you use it elsewhere — so the formula logic feels fine. The problem is that AVERAGEIFS has a specific set of failure modes that named ranges can quietly trigger, and Excel gives you almost no hint about which one you've hit.

This guide walks through every common cause of this error when named ranges are involved, with concrete diagnostic steps and targeted fixes for each scenario.

What You'll Learn

• Why AVERAGEIFS returns #DIV/0! even when your named range resolves correctly
• How to detect size mismatches between your average range and criteria ranges
• How workbook-level vs. sheet-level named range scope conflicts break criteria matching
• How to safely wrap AVERAGEIFS so empty results don't crash your dashboard
• How to rebuild a stale named range definition from scratch

What Causes AVERAGEIFS to Return #DIV/0!

AVERAGEIFS divides the sum of qualifying values by the count of rows that matched all criteria. If zero rows match, the count is zero and you get #DIV/0!. That's by design. The formula literally has nothing to average.

The tricky part: zero matching rows can happen for three very different reasons, each with its own fix.

• No data actually meets the criteria. The criteria values exist in the named range, but no corresponding rows in the data satisfy all conditions simultaneously.
• The criteria range and average range are different sizes. Excel silently misaligns the comparison and finds no matches even when they logically should exist.
• The named range resolves to the wrong cells. A scope conflict, stale definition, or formula error in the named range causes it to point somewhere unexpected.

When a hard-coded criteria value works but replacing it with a named range breaks the formula, the root cause is almost always either a size mismatch or a scope problem.

How Named Ranges Make the Problem Harder to Spot

With a literal value like "North" in your formula, you can see exactly what's being compared. With a named range, that transparency disappears. You write =AVERAGEIFS(Sales,Region,TargetRegion) and Excel evaluates TargetRegion at runtime — but you can't easily see what it resolved to without extra steps.

Named ranges also have scope: a name can be defined at the workbook level or tied to a specific worksheet. If you have two definitions of TargetRegion — one workbook-wide and one on Sheet2 — Excel picks one based on where the formula lives, and it might not be the one you intended.

Finally, named ranges that reference a multi-cell range (e.g., $D$2:$D$5) pass an array as the criteria argument. AVERAGEIFS doesn't average across multiple criteria values from a single named range the way you might expect — it uses the array comparison rules, which can produce surprising results or an outright error.

Diagnosing the Error: A Systematic Approach

Before you change anything, confirm exactly what's happening. Run through these checks in order.

Step 1 — Resolve the named range manually

Click the Name Box (the cell reference box at the top left of the sheet), type your named range name, and press Enter. Excel will select the cells the name points to. Verify those are the cells you expected.

Step 2 — Check the Formulas > Name Manager

Open Formulas > Name Manager and inspect the Refers To column for your named range. Make sure the address is correct, the scope is correct, and the range size matches what you intend. If the field shows a #REF! indicator, the named range itself is broken and needs to be rebuilt.

Step 3 — Temporarily substitute a literal value

Replace the named range in your formula with the literal value you expect it to contain. If the formula immediately returns a number, the data is fine and the named range is the culprit. If you still get #DIV/0!, the criteria genuinely match nothing and the problem is in your data logic, not the named range.

Step 4 — Use EVALUATE or a helper cell

In a blank cell, type =TargetRegion (or whatever your named range is called) and press Enter. If it's a single-cell name, you'll see the value. If it's a multi-cell name, you'll see just the first value, but it confirms the name resolves without error.

Fix 1: Correct a Size Mismatch Between the Average Range and Criteria Range

This is the most common cause when named ranges are involved. AVERAGEIFS requires the average_range and every criteria_range to be the same size (same number of rows and columns). If they differ by even one row, Excel may return #DIV/0! instead of raising a proper size error — depending on the version and context.

Suppose your average range is $C$2:$C$101 (100 rows), but your named range RegionList resolves to $B$2:$B$100 (99 rows). Excel's comparison loop steps through the criteria range row by row against the average range. The last row of the average range has no corresponding criteria row, so if matching only works on that final row, the result is zero matches and a #DIV/0!.

To fix this, open Name Manager, click your criteria range name, and update the Refers To field so the row count exactly matches the average range:

=Sheet1!$B$2:$B$101   ← corrected to match $C$2:$C$101

If you're using an Excel Table (ListObject), reference the table column directly in the formula instead of a named range pointing to fixed rows. Table columns resize automatically and always stay aligned:

=AVERAGEIFS(SalesTable[Amount],SalesTable[Region],TargetRegion)

This removes the alignment risk entirely because both ranges come from the same table structure. For more on how range offsets cause silent mismatches in related functions, see this walkthrough on fixing Excel SUMIF when sum range offset doesn't match the criteria range.

Fix 2: Resolve Named Range Scope Conflicts

When you have a named range that exists at both the workbook level and the sheet level, Excel always prefers the sheet-level definition for formulas on that sheet. This means a formula on Sheet1 using a name that exists at both scopes will silently use the Sheet1-scoped version, even if your intent was the workbook-wide version.

Open Name Manager and look for duplicate names. If you see the same name listed twice with different scopes, that's your conflict. Decide which definition you actually need:

• If you need the same range accessible from multiple sheets, delete the sheet-scoped duplicate and keep only the workbook-scoped definition.
• If you need sheet-specific ranges with the same logical name (e.g., each sheet has its own TargetRegion), make sure the formula is on the correct sheet and the sheet-scoped name points to the right cells on that sheet.

You can also make scope explicit in the formula by prefixing the sheet name when referencing a workbook-level name doesn't work as expected. This isn't standard practice but can help you confirm the scope during debugging:

=AVERAGEIFS(Sales,Region,Sheet1!TargetRegion)

Once you've confirmed which definition resolves correctly, clean up the duplicates in Name Manager so future formulas don't run into the same ambiguity.

Fix 3: Handle Empty or All-Excluded Named Range Criteria

If your named range points to a single cell that a user fills in — like a dropdown selection or a filter input — it may sometimes be blank. AVERAGEIFS with a blank criteria cell matches rows where the criteria column is also blank, which is rarely what you want and can produce unexpected zero-match results when your data has no blank rows in that column.

The pattern here is to guard the formula with an explicit check before AVERAGEIFS runs. Wrap it in an IF that checks whether the criteria cell is non-empty:

=IF(TargetRegion="","Select a region",AVERAGEIFS(Sales,Region,TargetRegion))

This prevents the formula from executing on a blank criteria value and gives the user a readable message instead of a confusing error. This is the same defensive approach described in detail for a related scenario: fixing AVERAGEIF #DIV/0! when all matching cells are filtered out.

For cases where the named range is multi-cell and you're trying to average across multiple criteria values, AVERAGEIFS won't do that in a single call. You'd need to use AVERAGEIF inside a SUMPRODUCT or SUM/COUNTIF pattern instead:

=IFERROR(
  SUMPRODUCT(AVERAGEIF(Region,TargetRegionList,Sales)*ISNUMBER(MATCH(Region,TargetRegionList,0)))
  /SUMPRODUCT((ISNUMBER(MATCH(Region,TargetRegionList,0)))*1),
  "No data"
)

That's heavier, but it's the correct tool when your criteria named range spans multiple cells and you want to average across all matching regions at once.

Fix 4: Rebuild a Broken or Stale Named Range

Named ranges can go stale when rows are inserted or deleted, when sheets are renamed, or when source data is moved. A stale named range shows a #REF! in Name Manager's Refers To field, and any formula using it inherits the error.

To rebuild it cleanly:

1. Open Formulas > Name Manager.
2. Select the broken name and click Delete. Confirm the deletion.
3. Select the correct range of cells on your sheet.
4. Click Formulas > Define Name, enter the exact same name, and confirm the scope.
5. Click OK and return to your AVERAGEIFS formula — it should now resolve correctly.

If the range you're naming is dynamic (it grows as new data is added), consider defining it using an OFFSET-based formula or, better, converting the underlying data to an Excel Table. Table column references expand automatically and never go stale. If you're using OFFSET in named range definitions, the guide on fixing Excel OFFSET #REF! when row or column arguments exceed sheet bounds covers the edge cases worth knowing about.

Using IFERROR and AVERAGEIF as Defensive Patterns

Even after fixing the root cause, it's good practice to wrap AVERAGEIFS in an IFERROR so a genuine zero-match scenario doesn't surface an error to end users.

=IFERROR(AVERAGEIFS(Sales,Region,TargetRegion,Quarter,TargetQuarter),0)

Returning zero when there's no match is appropriate in dashboards where the cell feeds a chart. Returning a text message is better in report cells where a human reads the value:

=IFERROR(AVERAGEIFS(Sales,Region,TargetRegion,Quarter,TargetQuarter),"No matching data")

Be careful not to use IFERROR as a substitute for actually understanding why the error occurs. If the formula is silently returning zero when it should return a real average, you've hidden a bug rather than fixed it. Use IFERROR only after you've confirmed the formula logic is correct and the error represents a genuinely expected empty-result scenario.

You might also combine AVERAGEIFS with COUNTIFS to validate before averaging. COUNTIFS uses identical criteria syntax and returns 0 instead of an error when nothing matches, making it a useful pre-check:

=IF(
  COUNTIFS(Region,TargetRegion,Quarter,TargetQuarter)=0,
  "No data",
  AVERAGEIFS(Sales,Region,TargetRegion,Quarter,TargetQuarter)
)

This pattern pairs naturally with the debugging techniques discussed in the article on fixing Excel COUNTIFS that returns wrong counts with date range criteria — the same criteria matching rules apply to both functions.

Common Pitfalls to Avoid

• Trailing spaces in criteria values. If your named range cell contains "North " (with a trailing space) and your Region column contains "North", nothing will match. Use TRIM on criteria input cells.
• Number-stored-as-text mismatches. A named range containing the number 2024 won't match a column where years are stored as text "2024". Standardize the data type on both sides.
• Wildcard characters in named range values. AVERAGEIFS supports wildcards in criteria strings, but if your named range value accidentally contains an asterisk or question mark, it will behave as a wildcard pattern rather than a literal character.
• Multi-area named ranges. A named range defined as a non-contiguous selection (e.g., $B$2:$B$10,$B$20:$B$30) cannot be used as a criteria_range in AVERAGEIFS. The function requires a single contiguous block.
• Cross-sheet named ranges as criteria. Named ranges that point to a different sheet from where the formula lives can cause unexpected results in some Excel versions. When possible, keep the average range, criteria ranges, and named range definitions on the same sheet, or use Table references that are inherently sheet-agnostic.

The hidden-row variant of this problem (where criteria appear to match but rows are excluded by filters) is covered thoroughly in the companion article on fixing Excel SUMIF returning a wrong total when the criteria range has hidden rows — the visibility rules apply to AVERAGEIFS the same way.

Wrapping Up: Next Steps

AVERAGEIFS #DIV/0! with a named range almost always traces back to one of four fixable problems: a size mismatch, a scope conflict, an empty or unexpected criteria value, or a stale named range definition. The key is to isolate which one before changing anything.

Here are the concrete actions to take right now:

• Open Name Manager and verify every named range used in the formula: check the Refers To address, the row count, and the scope. Fix any #REF! entries first.
• Substitute a literal value for the named range in your formula and see if the error disappears. If it does, the problem is the named range, not the data.
• Convert your data to an Excel Table and reference columns by their structured reference names. This eliminates size mismatches and stale definitions permanently.
• Add a COUNTIFS pre-check to confirm how many rows actually match your criteria before AVERAGEIFS tries to divide by that count.
• Wrap the final formula in IFERROR — but only after you understand why zero matches is a legitimate possibility, not a hidden bug.