Fix a Two-Dimensional Array Formula Returning #VALUE! in Excel
Question details
The user needs to identify why a multi-condition array formula returns #VALUE! when multiplying ranges from another worksheet.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Calculating a result from multiple conditions using arrays referenced from another worksheet.
- Observed behavior
- The array formula returns a #VALUE! error.
Select the cell containing the error and inspect its formula in the formula bar. Identify each multiplied range and the worksheet it refers to.
Check Range Dimensions and Worksheet References
Use this approach to correct mismatched arrays or references in the existing formula.
Select the formula cell and use the formula bar to compare every multiplied range. Make sure each has the same number of rows and columns; for example, A2:A10, B2:B10, and C2:C10 are all 9-row, 1-column ranges.
Check each sheet name in the formula bar against the worksheet tabs. Put names containing spaces in single quotes, as in 'Data Sheet'!A2:A10, and confirm that each reference points to the intended data.
If the formula is on the same sheet as its source data, try removing that sheet's name from the references, such as changing Sheet1!A2:A10 to A2:A10. Do not remove a sheet name when the source is on a different worksheet.
After editing the formula in the formula bar, press Enter in current Excel versions. In an older Excel version, select the formula cell, press F2, and press Ctrl+Shift+Enter if the formula requires legacy array entry.

Use SUMPRODUCT for a Multi-Condition Sum
Use SUMPRODUCT when the goal is to sum numeric values that meet multiple conditions.
Evaluate Multi-Condition Formulas in WPS Spreadsheets
Open your workbook in WPS Spreadsheets and use SUMPRODUCT with matching ranges to sum values that meet multiple conditions.
- 1. Open the workbook: Launch WPS Spreadsheets and choose Open to select your Excel workbook.
- 2. Review the source ranges: Open the source worksheet tab and identify condition and value ranges with matching row and column counts.
- 3. Enter SUMPRODUCT: Select a result cell and enter =SUMPRODUCT(--('Data Sheet'!A2:A10="East"),--('Data Sheet'!B2:B10="Open"),'Data Sheet'!C2:C10), replacing the example references and conditions with your own.
- 4. Check and save: Press Enter, verify the result against the source rows, and use File > Save As to save the workbook.

Frequently Asked Questions
Why do mismatched ranges cause #VALUE! in an array formula?
Multiplication pairs corresponding array elements. If the ranges have incompatible dimensions, Excel may return #VALUE!. Check the ranges in the formula bar and make their row and column counts identical.
Should I remove the worksheet name from every reference?
No. Removing a sheet name makes the reference point to the sheet containing the formula. Do this only when the source data is on that same sheet and the local reference is correct.
When do I need Ctrl+Shift+Enter?
Some array formulas in older Excel versions require legacy array entry. Edit the formula with F2 and press Ctrl+Shift+Enter. Current Excel versions generally use Enter for array formulas.
How do I reference a worksheet name that contains spaces?
Enclose the sheet name in single quotes before the exclamation mark, such as 'Data Sheet'!A2:A10. Check the spelling against the worksheet tab.
Can SUMPRODUCT replace every array formula?
No. SUMPRODUCT is useful for calculations such as sums based on multiple conditions, but it may not reproduce a formula that returns an array or performs a different calculation.




