Formula Errors

How to Fix #REF! Errors in Excel SUMIF and INDIRECT Formulas

Amos Gikunda Oct 12, 2026 869 views

Question details

A user is experiencing #REF! or zero results when combining SUMPRODUCT, SUMIF, and INDIRECT to aggregate pivot-table data across multiple worksheets based on a named range.

How to Fix #REF! Errors in Excel SUMIF and INDIRECT Formulas

Product: Excel
Device & OS: not provided
Scenario: The user is attempting to dynamically pull and sum data from various pivot tables situated on different worksheets using a 3D referencing formula setup.
Observed behavior: The formula returns a #REF! error when a generated sheet reference is invalid or misspelled, and returns 0 when the lookup text format doesn't exactly match the target cell's content.

Before you start

Ensure that all target worksheets actually exist in your workbook and that your named range list contains no extra spaces or typographical errors.

Solution 1Recommended

Verify Worksheet Names and INDIRECT Syntax

The #REF! error typically occurs because the INDIRECT function cannot resolve the generated text string into a valid worksheet reference, often due to typos in the named list.

When using INDIRECT to reference other sheets, Excel requires exact spelling. Furthermore, if your worksheet names contain spaces, the text string passed to INDIRECT must enclose the sheet name in single quotation marks.

Check named ranges for typos

Navigate to the list of worksheet names (e.g., 'PivotsList') in your workbook and ensure every name perfectly matches the actual sheet tabs.

Include single quotes in syntax

Wrap the sheet reference in single quotes within your formula. Structure the INDIRECT argument as "'"&PivotsList&"'!A3:A60" to safely handle spaces.

Evaluate the formula

Go to the Formulas tab and click 'Evaluate Formula'. Step through the calculation to identify exactly which sheet name is causing the reference to break.

Verify Worksheet Names and INDIRECT Syntax

Closed Workbooks: INDIRECT formulas will instantly return a #REF! error if they are attempting to reference a closed external workbook. Ensure all referenced files remain open.

Solution 2

Apply Wildcards for Partial Matches to Fix '0' Results

If your formula resolves the #REF! error but unexpectedly returns 0, your lookup value likely does not exactly match the target cell's contents.

Solution 3

Ensure Matching Range Dimensions

Mismatched criteria and sum ranges within SUMIF or SUMPRODUCT can lead to unexpected errors, including #VALUE! or calculation failures.

Troubleshoot Formulas Faster

Use WPS Spreadsheet to Master Complex Formulas

WPS Office Spreadsheet provides excellent formula auditing tools that make it easy to track down #REF! errors in dynamic functions like INDIRECT, SUMIF, and SUMPRODUCT.

1. Open your file in WPS: Launch WPS Spreadsheet and open the workbook containing the broken INDIRECT formula.
2. Access formula auditing: Navigate to the Formulas tab on the top ribbon and select 'Evaluate Formula'.
3. Step through calculations: Click 'Evaluate' sequentially to watch how WPS resolves your dynamic strings and pinpoint the exact source of the #REF! error.

100% compatible with Microsoft Excel formulas and syntaxBuilt-in 'Evaluate Formula' tool to trace errors step-by-stepFree and lightweight alternative for complex data analysisFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDIRECT formula return #REF! when referencing an external workbook?

The INDIRECT function requires the referenced workbook to be open in the background. If the target external file is closed, Excel cannot resolve the path dynamically and will return a #REF! error.

How do I fix a SUMPRODUCT formula that returns #VALUE! instead of #REF!?

A #VALUE! error usually indicates that the arrays inside your SUMPRODUCT formula have different dimensions. Ensure your criteria range and sum range span the exact same number of rows and columns.

Can I use INDIRECT to reference dynamically named tables?

Yes, you can construct a structured table reference as a text string inside the INDIRECT function. For example, INDIRECT(A1&"[[#Data],[Column1]]") will work perfectly as long as cell A1 contains a valid table name.

Excel & Data Problems