Fix SUMPRODUCT Returning Zero for a Spilled Named Range in WPS Spreadsheets
Question details
The user needs to fix a SUMPRODUCT formula that returns zero when it references a dynamic named range that spills correctly in the worksheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A dynamic array formula spills correctly in the worksheet, and the user references that spilled range through a named range in SUMPRODUCT.
- Observed behavior
- SUMPRODUCT returns zero even though the spilled named range displays the expected values.
Before changing the formula, open Name Manager and confirm whether the named range points to the spill anchor with the # operator, for example =Sheet1!$A$2#. Also verify that the spilled cells contain numeric values rather than text.
Point the Dynamic Named Range to the Spill Anchor
Use this first when a spilled array formula displays correctly but SUMPRODUCT returns zero. It makes the named range expand with the full spill range.
Dynamic array formulas expose a spill reference with the # suffix. If the named range is defined as a fixed reference or as only the formula result, SUMPRODUCT may receive zero cells or mismatched dimensions.
Click the Formulas tab in WPS Spreadsheets and select Name Manager to view the existing named ranges in the workbook.
Select the named range used in the SUMPRODUCT formula, click Edit, and set the Refers to box to the spill anchor, for example =Sheet1!$A$2#.
Click OK, then select the SUMPRODUCT formula cell and use Formulas > Calculate Now to refresh the result.
Check that SUMPRODUCT Arrays Are Numeric and Have Identical Dimensions
Use this if the spill reference is correct but the result is still zero. Zero often results from text values, booleans treated as text, or unequal range sizes.
Work with SUMPRODUCT and Dynamic Named Ranges in WPS Spreadsheets
Use WPS Spreadsheets to create spill references with the # operator, manage named ranges in Name Manager, and debug SUMPRODUCT calculations without switching to another suite.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the .xlsx workbook containing the spilled named range and SUMPRODUCT formula.
- 2. Open Name Manager: Click the Formulas tab and choose Name Manager to review and edit the named range definition.
- 3. Apply a spill anchor reference: Set the named range to the spill anchor using the # suffix, for example =Sheet1!$A$2#, and confirm the spilled values are numeric.
- 4. Recalculate and verify: Select the SUMPRODUCT cell, then click Formulas > Calculate Now to refresh the result and confirm it no longer returns zero.
Frequently Asked Questions
Why does SUMPRODUCT return zero when my spilled range shows values?
Usually the named range is not pointing to the spill anchor. Edit the name in Formulas > Name Manager and set the reference to the spill range with the # suffix, such as =Sheet1!$A$2#, then verify the spilled cells contain numeric values.
How do I reference a spilled dynamic array in a named range in WPS Spreadsheets?
In Name Manager, define the named range as =Sheet1!$A$2#. The # operator tells WPS Spreadsheets to include the entire spilled array starting from cell A2.
Does WPS Spreadsheets support the # spill operator in named ranges?
Yes. When your version of WPS Spreadsheets supports dynamic arrays, you can use the # suffix after the anchor cell to reference the full spill range in formulas and named ranges.
What should I do if the SUMPRODUCT result is still zero after using the spill anchor?
Check that every SUMPRODUCT array argument has the same dimensions and that the spilled cells are numbers, not text. Use ISNUMBER to test a cell, convert text numbers with VALUE, and then recalculate with Formulas > Calculate Now.
