Fix SUMPRODUCT and INDIRECT #REF! Error Across Multiple Sheets in Excel
Question details
The user needs to resolve a #REF! error occurring in a SUMPRODUCT formula that uses COUNTIF and INDIRECT to reference a list of worksheet names stored in a cell range.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Calculating data dynamically across multiple worksheets by maintaining a list of sheet names in a specific cell range.
- Observed behavior
- The formula returns a #REF! error because the INDIRECT function fails to process one or more worksheet names, either due to an invalid name in the list or disabled macro settings.
Verify that your referenced range does not contain any blank cells or hidden characters, as passing an empty string to the INDIRECT function will immediately trigger a #REF! error.
Verify and Correct Worksheet Names in the Reference Range
Ensure every cell in your referenced list contains an exact, valid worksheet name to prevent the INDIRECT function from failing.
Highlight the specific range in your worksheet (e.g., H5:H145) that contains the list of sheet names your INDIRECT formula is calling.
Compare each text string in the cells exactly with the names on the worksheet tabs. Ensure there are no typos, extra spaces, or missing punctuation marks.
If your worksheet names contain spaces (e.g., 'Sales Data'), verify that your INDIRECT formula string encloses the sheet name in single quotes, such as: "'"&H5&"'!A1:A10".
Once corrections are made, press F9 or navigate to the Formulas tab and click 'Calculate Now' to refresh the SUMPRODUCT formula.
Enable Excel 4.0 Macros for Dynamic Sheet Names
If your worksheet names are generated dynamically using legacy Excel 4.0 macro functions, you must enable them in the Trust Center for the list to populate properly.
Consolidate Worksheets into a Single Database
For long-term stability and easier maintenance, consider combining similarly structured sheets into one database rather than relying on complex INDIRECT formulas.
Effortlessly Manage Complex Formulas in WPS Spreadsheet
WPS Office provides a highly compatible and powerful Spreadsheet tool designed to help you easily handle advanced array formulas like SUMPRODUCT and INDIRECT. It efficiently processes 3D referencing across multiple sheets with built-in error checking to help you resolve issues like #REF! instantly.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your multiple worksheets.
- 2. Input your formula: Select the target cell and enter your `=SUMPRODUCT(COUNTIF(INDIRECT(...)))` formula exactly as you would normally.
- 3. Use Error Checking: If a #REF! error appears, go to the Formulas tab and click 'Error Checking' to instantly trace the exact cell causing the invalid reference.
- 4. Evaluate the formula: Click 'Evaluate Formula' in the same tab to watch the formula calculate step-by-step, making it easy to spot exactly which sheet name is failing.
Frequently Asked Questions
Why does the INDIRECT function return a #REF! error?
The INDIRECT function returns a #REF! error if the referenced cell evaluates to an invalid cell reference, a misspelled worksheet name, a closed external workbook, or a blank cell.
How do I handle worksheet names with spaces in an INDIRECT formula?
If your worksheet name contains spaces, you must enclose the sheet name in single quotation marks within the formula text string. For example, your formula should look like: `=INDIRECT("'"&A1&"'!B2")`.
Are there better alternatives to using SUMPRODUCT and INDIRECT across multiple sheets?
Yes. If your spreadsheet software supports dynamic arrays, you can use 3D references combined with functions like VSTACK and FILTER. These modern functions are less volatile, faster to calculate, and less prone to #REF! errors compared to INDIRECT.
