Function Problems

WPS Spreadsheet SUMPRODUCT to Find a Value at Two-Criteria Intersection

Ayan MasoodAyan Masood Nov 12, 2026 870 views

Question details

The user needs to return a value from a data array when one criterion identifies a row and another identifies a column.

Use SUMPRODUCT in WPS Spreadsheet to Find a Value at a Two-Criteria Intersection
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

In WPS Spreadsheet, click the cell where you want the matching value to appear.

2
Enter the SUMPRODUCT formula

Type =SUMPRODUCT((A2:A4)*(B2:D4=C9)*(B1:D1=C8)) into the formula bar or selected cell.

3
Confirm the formula

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.

4
Check the criteria if needed

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.

Use SUMPRODUCT to match the row and column criteria
No helper columns required: This SUMPRODUCT approach performs the two-criteria intersection directly in one formula.
WPS Spreadsheet

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. 1. Open WPS Spreadsheet: Launch WPS Office and open the workbook containing the lookup table.
  2. 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. 3. Enter the formula: Select the destination cell and enter =SUMPRODUCT((A2:A4)*(B2:D4=C9)*(B1:D1=C8)).
  4. 4. Confirm and review: Press Enter, then verify that the returned value corresponds to both the selected row and selected column.
Evaluate SUMPRODUCT formulas for two-dimensional criteria lookups.Return the matching intersection value without helper columns.Use a familiar spreadsheet interface for entering and checking formulas.Open and save Microsoft Excel XLSX files with compatible spreadsheet formatting.
microsoft office alternative - wps office

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.