Formula Errors

Fix SUMPRODUCT Dynamic Worksheet Selection #REF! Error in Excel

Adam Davis Oct 15, 2026 869 views

Question details

The user needs to calculate values across multiple dynamically selected worksheets using SUMPRODUCT, but the formula returns an error.

How to Fix SUMPRODUCT with Dynamic Worksheet Selection #REF! Error

Product: Excel
Device & OS: not provided
Scenario: Attempting to use a three-dimensional worksheet reference inside a SUMPRODUCT formula to sum identical cells across selected sheets.
Observed behavior: The formula returns a #REF! error because SUMPRODUCT does not directly support standard three-dimensional worksheet references.

Before you start

Ensure you have typed the exact names of all target worksheets in a helper column, as the INDIRECT function relies on perfectly matched text to build valid references.

Solution 1Recommended

Use INDIRECT and CELL functions for single-cell cross-sheet calculation

Replace the unsupported 3D reference in SUMPRODUCT by dynamically building sheet references using the INDIRECT and CELL functions.

Because the SUMPRODUCT function does not natively support standard three-dimensional references (e.g., Sheet1:Sheet3!A1), directly selecting multiple sheets will cause a #REF! error.

You can bypass this limitation by listing the worksheet names in a cell range and converting them into valid structural references using INDIRECT.

Create a worksheet name list

In your summary sheet, type the exact names of the worksheets you want to sum in a column range, for example, B2:B6.

Set up a condition column (Optional)

In the adjacent column (E2:E6), enter a '1' to include the corresponding worksheet in the sum, or a '0' to exclude it.

Enter the dynamic SUMPRODUCT formula

Select the cell where you want the final total. Type the formula: =SUMPRODUCT(N(INDIRECT("'"&$B$2:$B$6&"'!"&CELL("Address",A1))),$E$2:$E$6)

Calculate the result

Press Enter. The formula dynamically targets cell A1 on every worksheet listed in B2:B6, multiplying the value by your 0/1 condition to output the sum.

Use INDIRECT and CELL functions for single-cell cross-sheet calculation

Volatile Functions Notice: The INDIRECT function is volatile, meaning it recalculates every time a change occurs in the workbook. Excessive use in large files may slow down calculation performance.

Solution 2

Use SUMIF with INDIRECT for summing a range across sheets

If you need to calculate a range of cells rather than a single cell across multiple worksheets, wrapping SUMIF inside SUMPRODUCT is the best approach.

Efficient Data Calculation

Manage Cross-Sheet Formulas Seamlessly in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, allowing you to easily manage complex dynamic calculations using SUMPRODUCT and INDIRECT. Handle your data analysis efficiently without compatibility worries.

1. Open your workbook in WPS: Launch WPS Spreadsheet and open the document containing your cross-sheet data.
2. List your sheet names: Type the names of the worksheets you intend to calculate into a helper column.
3. Apply the INDIRECT array formula: Input the dynamic formula combining SUMPRODUCT, INDIRECT, and CELL, just as you would in standard Excel.
4. Press Enter: Hit Enter to immediately return the sum of the specified cell across multiple dynamically referenced worksheets.

100% compatibility with Microsoft Excel formulas and .xlsx filesProcesses complex array functions and cross-sheet references smoothlyFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does SUMPRODUCT return a #REF! error with 3D references?

SUMPRODUCT is designed to handle arrays but does not natively interpret 3D references (such as Sheet1:Sheet3!A1). When it tries to process one, it fails to locate a valid array structure, resulting in a Reference (#REF!) error.

Which functions can natively support 3D references across worksheets?

Basic calculation functions like SUM, AVERAGE, MIN, MAX, and COUNT natively support 3D references. You can simply write =SUM(Sheet1:Sheet3!A1) without needing workarounds like INDIRECT.

Will my INDIRECT formula update automatically if I add a new sheet?

No. Because INDIRECT relies on a static text list of worksheet names (e.g., B2:B6), you must manually type the new worksheet's name into your helper column and extend the formula's range so the new sheet is included.

Excel & Data Problems