Fix WPS Spreadsheet Structured Reference Formulas Returning Only the Current Row
Question details
The user needs to aggregate values from an Excel-style table without the structured reference being reduced to the current row.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A SUM formula uses the Tracking table's Amount and Type columns to total rows marked as Expenses.
- Observed behavior
- The formula returns 1,200 instead of the expected 1,350, indicating that the table reference may be evaluated as a current-row reference through implicit intersection or that a matching Type value contains extra spaces.
Check the table name and column headers in the Table tools or Name Box, then inspect every matching Type cell for trailing spaces such as "Expenses ". Confirm that the Amount values are numeric rather than text.
Use SUMIFS with the table columns for criteria-based totals
Use this recommended approach when you need to add all Amount values whose Type is exactly Expenses.
SUMIFS explicitly treats the structured columns as ranges and applies the Type criterion to every table row, avoiding a current-row-only result.
In WPS Spreadsheet, click the cell where the total should appear.
Type =SUMIFS(Tracking[Amount],Tracking[Type],"Expenses") and press Enter. Replace Tracking, Amount, or Type if your table uses different names.
Review the Tracking[Type] cells and make sure each intended value is exactly Expenses with no leading or trailing spaces.
Use SUMPRODUCT for a flexible structured-reference calculation
Use this alternative when you need a formula that multiplies a condition by the Amount column or combines multiple array conditions.
Check the table reference and data types
Use these checks when both SUMIFS and SUMPRODUCT produce an unexpected total.
Calculate accurate table totals with WPS Spreadsheet
WPS Spreadsheet lets you use SUMIFS and SUMPRODUCT with Excel-compatible structured references to total table data by criteria, inspect inconsistent labels, and maintain formulas in familiar XLSX workbooks.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the Tracking table.
- 2. Select the total cell: Click the cell where you want the Expenses total to appear.
- 3. Enter a criteria-based formula: Enter =SUMIFS(Tracking[Amount],Tracking[Type],"Expenses") and press Enter.
- 4. Review and correct source values: Inspect the Type column for trailing spaces and the Amount column for numbers stored as text.
Frequently Asked Questions
Why does my structured reference formula return only the current row?
The formula may be subject to implicit intersection, which reduces a table column reference to the value from the current row. Use =SUMIFS(Tracking[Amount],Tracking[Type],"Expenses") to aggregate the entire table column by criteria.
What formula sums all Expenses in an Excel-style table?
In WPS Spreadsheet, enter =SUMIFS(Tracking[Amount],Tracking[Type],"Expenses"). This adds numeric values in the Amount column when the corresponding Type value is exactly Expenses.
Why is one Expenses row missing from the total?
Check for leading or trailing spaces, such as Expenses followed by a space. SUMIFS requires an exact match, so correct the cell or remove the extra spaces before recalculating.
Can I use SUMPRODUCT instead of SUMIFS with table references?
Yes. Enter =SUMPRODUCT(Tracking[Amount]*(Tracking[Type]="Expenses")). It evaluates the condition for each row and sums the matching Amount values, provided the Amount column contains numeric data.
