Formula Errors

How to Fix #REF! Error in Excel SUMPRODUCT and INDIRECT Formulas Across Worksheets

Natalie Taylor Oct 22, 2026 869 views

Question details

The user needs to resolve a #REF! error that occurs when utilizing SUMPRODUCT and INDIRECT formulas to sum data across multiple worksheet tabs.

How to Fix #REF! Error with SUMPRODUCT and INDIRECT Across Excel Worksheets

Product: Excel
Device & OS: not provided
Scenario: Attempting to consolidate and sum data across multiple worksheets by passing a range of worksheet names into the INDIRECT function within a SUMPRODUCT or SUMIF formula.
Observed behavior: The formula returns a #REF! error because the referenced range of sheet names includes blank cells or invalid names that do not match the existing worksheet tabs.

Before you start

Double-check the exact spelling of your worksheet tabs and ensure there are no trailing spaces, as mismatched names or extra spaces are a common cause of formula referencing errors.

Solution 1Recommended

Adjust the Worksheet Name Reference Range

Modify your formula to strictly reference only the cells that contain valid and existing worksheet names, omitting any blank cells.

When the INDIRECT function processes an array of sheet names, every single cell in that array must evaluate to a valid worksheet in your file. If your designated range is larger than your actual list of sheet names (e.g., referencing B2:B21 when only B2:B4 contain data), the empty cells evaluate as invalid sheet names, causing the entire SUMPRODUCT formula to collapse into a #REF! error.

Locate your sheet name list

Identify the exact range of cells in your workbook where you have typed the names of the worksheets you want to sum across.

Check for empty cells

Ensure that the list does not contain any blank rows. For instance, if your list is typed in B2, B3, and B4, cell B5 and below should not be included in the formula's range.

Update the INDIRECT formula

Click on the cell containing your SUMPRODUCT formula. Change the range inside the INDIRECT function to perfectly match the populated list. Update the text from INDIRECT("'"&B2:B21&"'!A1:A10") to INDIRECT("'"&B2:B4&"'!A1:A10").

Press Enter to calculate

Press Enter to apply the updated formula. The #REF! error should disappear and display the correctly summed value.

Adjust the Worksheet Name Reference Range

Formula Evaluation: Restricting the array only to non-blank, existing worksheet names guarantees that INDIRECT resolves correctly for each element in the array.

Solution 2

Create a Dynamic Named Range for Sheet Names

Use a dynamic named range to automatically adjust the formula's scope so it ignores blank cells even when you add new worksheet names later.

Resolve Formula Errors Easily

Sum Data Across Sheets Effortlessly in WPS Office

WPS Spreadsheet provides robust support for complex array formulas like SUMPRODUCT and INDIRECT. You can easily manage multi-sheet data consolidation and quickly track down #REF! errors with its intuitive error-checking tools.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your multi-sheet data.
2. List valid worksheet names: Type the exact names of the tabs you wish to consolidate into a contiguous range of cells, ensuring no blanks are included.
3. Enter the formula: Type your SUMPRODUCT and INDIRECT formula, selecting the specific range containing your valid sheet names.
4. Calculate the result: Press Enter to instantly execute the array formula and get the consolidated sum without encountering reference errors.

100% compatible with Microsoft Excel formulas, functions, and file formatsAdvanced error-checking tools to quickly isolate blank cells and invalid referencesFree, lightweight, and fast alternative to heavy spreadsheet applicationsFamiliar user interface ensuring seamless migration with no learning curve

Frequently Asked Questions

Why does my INDIRECT formula return #REF! even if the sheet name exists?

This commonly happens if the worksheet name contains spaces or special characters but is not enclosed in single quotation marks within your formula. Ensure your formula is formatted as INDIRECT("'" & A1 & "'!B1") to properly handle spaces.

Can I use SUMPRODUCT and INDIRECT to reference closed workbooks?

No. The INDIRECT function requires the referenced external workbook to be actively open in your spreadsheet application. If the external file is closed, the formula will automatically return a #REF! error.

How can I check which cell is causing the #REF! error in my array?

You can highlight the INDIRECT portion of your formula in the formula bar and press F9. This will evaluate that specific part of the array, allowing you to see exactly which sheet name in the array is evaluating to a #REF! error.

Excel & Data Problems