How to Use SUMIFS or XLOOKUP to Sum Multiple Scores by Identifier in WPS Spreadsheets
Question details
The user needs to sum multiple scores associated with an identifier in WPS Spreadsheets, where scores may be arranged in rows or columns and a VLOOKUP formula returns #N/A due to lookup range layout.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A spreadsheet contains identifiers in one column and score values either in a single score column across multiple rows or in four score columns per identifier; the user wants a formula to total all matching scores.
- Observed behavior
- A VLOOKUP formula returns #N/A when the identifier is not in the first column of the lookup range or when the data layout does not match a multi-column lookup.
Before you start, confirm whether each identifier appears once or multiple times and note the exact sheet name and score columns. If you plan to use XLOOKUP, verify that your WPS Spreadsheets version supports dynamic array functions.
Sum scores stored in rows with SUMIFS in WPS Spreadsheets
Use this solution when the same identifier can appear multiple times and the score is stored in a single score column.
VLOOKUP is not suitable for repeated identifiers because it returns only the first match. SUMIFS adds every score where the identifier column matches the lookup value.
Click the cell where you want the total score to appear.
Type =SUMIFS('DNAv ORS scores'!D:D,'DNAv ORS scores'!A:A,A3) and press Enter. Replace the sheet name, score column, identifier column, and identifier cell to match your workbook.
Drag the fill handle down or double-click it to copy the formula to other identifier rows.
Sum four score columns for one identifier with SUM and XLOOKUP
Use this solution when each identifier appears once and the four scores are stored in adjacent columns such as D:G.
Sum Scores by Identifier in WPS Spreadsheets with SUMIFS and XLOOKUP
Replace error-prone VLOOKUP formulas by using SUMIFS for row-based scores or SUM with XLOOKUP for column-based scores in WPS Spreadsheets. The formulas work with existing workbooks and remain compatible with Microsoft Excel files.
- 1. Open your workbook: Launch WPS Office and open the workbook that contains the scores and identifiers.
- 2. Check the score layout: Look at the sheet to determine whether scores are stored in a single score column with repeated identifiers or in four columns such as D:G for each identifier.
- 3. Use SUMIFS for row-based scores: Click the total cell and type =SUMIFS('DNAv ORS scores'!D:D,'DNAv ORS scores'!A:A,A3), then press Enter.
- 4. Use SUM and XLOOKUP for column-based scores: Click the total cell and type =SUM(XLOOKUP(A3,'DNAv ORS scores'!A:A,'DNAv ORS scores'!D:G)), then press Enter.
- 5. Fill down and save: Drag the fill handle to apply the formula to other identifiers, then press Ctrl+S to save the workbook as .xlsx.
Frequently Asked Questions
Why does VLOOKUP return #N/A when summing scores by identifier?
VLOOKUP requires the identifier to be in the first column of the lookup range and returns only the first match, not a total. Use SUMIFS for repeated identifiers or SUM with XLOOKUP for multiple score columns.
How do I sum scores when the same identifier appears in multiple rows?
Use =SUMIFS(score_column, identifier_column, identifier_cell), for example =SUMIFS('DNAv ORS scores'!D:D,'DNAv ORS scores'!A:A,A3).
How do I sum four score columns for one identifier in WPS Spreadsheets?
If the identifier appears once and the scores are in columns D:G, use =SUM(XLOOKUP(A3,'DNAv ORS scores'!A:A,'DNAv ORS scores'!D:G)).
What if my WPS Spreadsheets version does not support XLOOKUP?
Use SUMIFS for row-based data. For column-based data, you can use an INDEX/MATCH combination or SUMPRODUCT to sum the matched range.
Do these formulas work in both WPS Spreadsheets and Microsoft Excel?
Yes, SUMIFS and SUM with XLOOKUP work in WPS Spreadsheets versions that support them and in Microsoft Excel, and .xlsx workbooks remain compatible.
