Formula Errors

Fix a Two-Dimensional Array Formula Returning #VALUE! in Excel

Nimra MalikNimra Malik Nov 18, 2026 868 views

Question details

The user needs to identify why a multi-condition array formula returns #VALUE! when multiplying ranges from another worksheet.

Fix a Two-Dimensional Array Formula Returning #VALUE! in Excel
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.
Before you start

Select the cell containing the error and inspect its formula in the formula bar. Identify each multiplied range and the worksheet it refers to.

Solution 1Recommended

Check Range Dimensions and Worksheet References

Use this approach to correct mismatched arrays or references in the existing formula.

1
Compare the ranges

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.

2
Verify sheet references

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.

3
Check local references

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.

4
Confirm the formula entry

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.

Check Range Dimensions and Worksheet References
Check cell values: If the dimensions and references are correct, inspect the source cells for errors or text being used in arithmetic. An error in a referenced cell can propagate to the formula result.
Calculate with WPS Spreadsheets

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. 1. Open the workbook: Launch WPS Spreadsheets and choose Open to select your Excel workbook.
  2. 2. Review the source ranges: Open the source worksheet tab and identify condition and value ranges with matching row and column counts.
  3. 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. 4. Check and save: Press Enter, verify the result against the source rows, and use File > Save As to save the workbook.
Calculate multi-condition sums with SUMPRODUCTOpen and save Microsoft Excel XLSX and XLS filesReview worksheet references in a familiar spreadsheet interfaceUse WPS Office for free
microsoft office alternative - wps office

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.