How to Use Excel SUMIFS and SUMPRODUCT to Match Headers and Rows in WPS Spreadsheet
Question details
Learn how to write Excel SUMIFS and SUMPRODUCT formulas to sum values when a header must match or both a row label and a column header must match in WPS Spreadsheet.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- User has a data range with a header row and row labels, and needs to return totals based on a selected header or on both a selected header and row label.
- Observed behavior
- The correct formula returns the matching total from a simple horizontal range with SUMIFS, and from a two-dimensional data block with SUMPRODUCT.
Open your workbook in WPS Spreadsheet and identify whether the criteria are only in a single header row or in both row labels and column headers before choosing the formula.
Use SUMIFS When Only a Horizontal Header Row Must Match
Use this when your data is in a simple horizontal range and you only need to match one column header.
SUMIFS works best when the sum range and criteria range are both in a single row, so the formula returns the value that matches the selected header.
Click the cell where the result should appear, for example K2, next to the row you are summing.
Type =SUMIFS($C2:$F2,$C$1:$F$1,J$1) and press Enter. The sum range is C2:F2 and the criteria range is C1:F1 matched against J1.
Use SUMPRODUCT When Row Labels and Column Headers Must Both Match
Use this when the data is a two-dimensional block and the formula must apply both a row condition and a column-header condition.
Write SUMIFS and SUMPRODUCT Matching Formulas in WPS Office
Open your workbook in WPS Spreadsheet, use SUMIFS for single-row header matching and SUMPRODUCT for two-dimensional row and column criteria, and calculate the totals without switching to Microsoft Excel.
- 1. Open WPS Office Spreadsheet: Launch WPS Spreadsheets and open the workbook containing your header row and row labels.
- 2. Click the result cell: Select the cell where the SUMIFS or SUMPRODUCT total should appear.
- 3. Type the formula: Enter =SUMIFS($C2:$F2,$C$1:$F$1,J$1) for a header-only match, or =SUMPRODUCT(($C$1:$F$1=J$1)*($A$2:$A$7=$I2)*$C$2:$F$7) for row and column matches.
- 4. Press Enter and verify: Press Enter and confirm that the returned total matches the selected header and row criteria.
Frequently Asked Questions
When should I use SUMIFS instead of SUMPRODUCT for matching headers?
Use SUMIFS when the data to sum is in a single row and only the header row must match, such as =SUMIFS($C2:$F2,$C$1:$F$1,J$1).
Can WPS Spreadsheet open Excel files with these formulas?
Yes, WPS Office Spreadsheet supports .xlsx and .xls files, and Excel-style SUMIFS and SUMPRODUCT formulas work without conversion.
Why does my SUMPRODUCT formula return #VALUE! or 0?
Check that all ranges in the formula have the same dimensions. Mismatched ranges in WPS may cause a #VALUE! error, while unmatched criteria can return 0.
How do I fix absolute and relative references in these formulas?
Keep data ranges and header ranges absolute with $ signs so copying the formula still uses the correct header row and label column.
Does SUMPRODUCT work if my row labels are in A2:A7 and headers in C1:F1?
Yes. Use =SUMPRODUCT(($C$1:$F$1=J$1)*($A$2:$A$7=$I2)*$C$2:$F$7) and adjust the target cells to match your own workbook.
