WPS Spreadsheet SUMPRODUCT to Find a Value at Two-Criteria Intersection
Question details
The user needs to return a value from a data array when one criterion identifies a row and another identifies a column.

- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet stores return values in the first column, row-related data in a two-dimensional range, column headers above that range, and the two lookup criteria in C9 and C8.
- Observed behavior
- The goal is to use one SUMPRODUCT formula to match both criteria and return the corresponding value without creating helper columns.
Confirm that A2:A4 contains the values to return, B2:D4 contains the criteria matrix, B1:D1 contains the column headers, C9 contains the row criterion, and C8 contains the column criterion. Make sure the criteria use the same text or numeric format as the source data.
Use SUMPRODUCT to match the row and column criteria
Use this formula when two criteria identify a single cell in a two-dimensional range and the result is stored in the first column of the array.
The first condition checks the row-related values against C9, while the second checks the headers against C8. SUMPRODUCT multiplies the matching arrays by A2:A4 and returns the value associated with the matching row and column.
In WPS Spreadsheet, click the cell where you want the matching value to appear.
Type =SUMPRODUCT((A2:A4)*(B2:D4=C9)*(B1:D1=C8)) into the formula bar or selected cell.
Press Enter to calculate the result. WPS Spreadsheet evaluates the row criterion in C9 and the column criterion in C8, then returns the corresponding value from A2:A4.
If the result is 0 or unexpected, compare C8 with the headers in B1:D1 and C9 with the row values in B2:D4, checking for extra spaces or mismatched number and text formats.

Find Two-Criteria Intersections Directly in WPS Spreadsheet
WPS Spreadsheet lets you enter and evaluate the SUMPRODUCT formula directly, making it easy to match a row criterion and a column header without helper columns while keeping your worksheet compatible with familiar spreadsheet workflows.
- 1. Open WPS Spreadsheet: Launch WPS Office and open the workbook containing the lookup table.
- 2. Prepare the lookup layout: Place the return values in A2:A4, the criteria matrix in B2:D4, the headers in B1:D1, and the criteria in C8 and C9.
- 3. Enter the formula: Select the destination cell and enter =SUMPRODUCT((A2:A4)*(B2:D4=C9)*(B1:D1=C8)).
- 4. Confirm and review: Press Enter, then verify that the returned value corresponds to both the selected row and selected column.

Frequently Asked Questions
What does each part of the SUMPRODUCT formula do?
(A2:A4) provides the possible return values, (B2:D4=C9) identifies the matching row, and (B1:D1=C8) identifies the matching column. SUMPRODUCT combines these conditions and returns the corresponding value.
Where should the two lookup criteria be entered?
Enter the column-header criterion in C8 and the row criterion in C9, as referenced by the formula =SUMPRODUCT((A2:A4)*(B2:D4=C9)*(B1:D1=C8)).
Can this formula work without helper columns?
Yes. The SUMPRODUCT formula evaluates both criteria directly, so no additional helper columns are required.
Why might the formula return 0?
Check that C8 exactly matches one of the headers in B1:D1 and C9 matches the relevant value in B2:D4. Also remove extra spaces and ensure numbers are not stored as text.
Can I use this formula in an Excel workbook opened in WPS Spreadsheet?
Yes. WPS Spreadsheet supports SUMPRODUCT and can open and save common Microsoft Excel XLSX workbooks. Verify the source ranges and criteria references after opening the file.




