How to Use a Table Column as SUMIF Criteria in WPS Office Spreadsheets
Question details
The user needs to replace an invalid array constant with a structured table reference so SUMIF can use a table column as its criteria inside SUMPRODUCT in WPS Office Spreadsheets.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a SUMPRODUCT(SUMIF(...)) formula and wants to reference a column in an Excel Table as the SUMIF criteria.
- Observed behavior
- The formula containing a literal array constant with a table expression fails because array constants cannot include structured references.
Open the workbook in WPS Office Spreadsheets and verify that the source range is formatted as an Excel Table. Note the exact table name and column header, such as SIA[Phases SIA], before editing the formula.
Replace the Array Constant with a Direct Structured Table Reference
Use this when the formula currently wraps the table column in curly braces, causing an invalid array constant error.
Literal array constants cannot contain a table expression. SUMIF can accept a structured table reference directly as its criteria argument, and SUMPRODUCT can then calculate the total for all matching criteria.
Click the cell that contains the failing SUMPRODUCT(SUMIF(...)) formula, then click anywhere in the Formula Bar to edit it.
Delete the opening and closing curly braces around the table column. Change the criteria argument from a braced table expression to the direct structured reference SIA[Phases SIA].
Type or paste =SUMPRODUCT(SUMIF($A:$A,SIA[Phases SIA],F:F)) and press Enter. If WPS Office suggests a table reference while typing, click the matching SIA[Phases SIA] entry.
Check that the cell now returns the expected total. If the table name or column header is different, update it to match the workbook exactly.
Verify Criteria Values and Sum Range Alignment
Use this if the formula returns zero or an unexpected total after removing the array constant.
Calculate SUMIF with a Table Column Criteria in WPS Office
WPS Office Spreadsheets lets you build the same SUMPRODUCT(SUMIF(...)) formula with structured table references, so you can replace the invalid array constant and get the result without changing your workbook.
- 1. Open your Excel workbook in WPS Office Spreadsheets: Download and launch WPS Office, then open the workbook that contains the SUMPRODUCT(SUMIF(...)) formula.
- 2. Click the formula cell: Select the cell with the invalid array constant formula and click the Formula Bar to edit it.
- 3. Enter the structured table reference: Replace the braced table expression with the direct reference, for example =SUMPRODUCT(SUMIF($A:$A,SIA[Phases SIA],F:F)).
- 4. Press Enter and verify: Press Enter to calculate the formula and check that the total matches the expected sum.
Frequently Asked Questions
Why can't I use a table column inside an array constant in Excel SUMIF?
An array constant cannot include structured references. In WPS Office Spreadsheets, remove the curly braces from around the table column and use it directly as the SUMIF criteria, for example SIA[Phases SIA].
Can SUMIF use a table column as criteria without SUMPRODUCT?
Yes. A formula such as =SUMIF($A:$A,SIA[Phases SIA],F:F) works directly, but SUMPRODUCT is used to return the combined total for all matching criteria.
What should I check if my SUMPRODUCT(SUMIF(...)) formula returns 0?
Verify that the values in the criteria table column exactly match the values in column A, and confirm the sum range in column F aligns with the same rows as the criteria range.
Do table structured references need special brackets in WPS Spreadsheets?
Structured references use TableName[ColumnName] syntax. The table column reference itself includes square brackets, but do not wrap the entire criteria in curly braces.
Does WPS Office support Excel table structured references?
Yes. WPS Office Spreadsheets supports Excel table structured references and Excel formula functions such as SUMIF and SUMPRODUCT.
