How to Keep the Highest Score by Category and Sum Results by Name in WPS Spreadsheets
Question details
The user needs to keep only the highest score for each name and category combination in an Excel table, then sum those retained scores to get a total score for every name using modern Excel functions.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook contains score records with Name, Category, and Score columns, and the user wants a dynamic summary that totals each person's best result per category.
- Observed behavior
- The goal is to produce a spill range showing each unique name and the total of their highest category scores without using manual filtering or deleting duplicate rows.
Make sure your WPS Office version supports dynamic array functions such as LET, SCAN, LAMBDA, UNIQUE, FILTER, and SORT. Store the data in an Excel Table named scores with Name, Category, and Score columns, or adjust the references in the formula to match your real range.
Use a dynamic array formula to keep only the highest category score and sum by name
Use this solution when your records are in a table named scores and you want one spill formula to return each unique name with the sum of that person's highest score per category.
The formula filters the table for each name, sorts the person's Category and Score columns in descending order, and keeps the first occurrence of each category, which is the highest score because of the sort. It then sums those kept scores and stacks the results into a two-column output.
Click the cell where you want the dynamic result to start, such as cell E1.
Type or paste the formula below and press Enter. It reads the Excel Table scores and returns a two-column spill range with Name and Score.
If Category and Score are not the second and third columns of your table, update {2,3} in CHOOSECOLS to match the actual column positions, and keep the sort order as {1,2}.
Check that each name appears once and that the displayed total is the sum of that person's highest score for each category.
Calculate highest category scores and totals in WPS Spreadsheets
WPS Spreadsheets supports modern dynamic array functions, so you can apply this single-formula summary directly in a WPS workbook. The result updates automatically when names, categories, or scores change.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open the workbook that contains the scores table.
- 2. Select the output cell: Click the cell where you want the dynamic result to start, such as E1.
- 3. Enter the dynamic array formula: Type the formula shown in the solution above and press Enter to return unique names and their total highest category scores.
- 4. Save the workbook: Go to Menu > Save As and choose Excel Workbook (*.xlsx) if you need to share the file with Microsoft Excel users.
Frequently Asked Questions
Does this formula work in WPS Spreadsheets?
Yes, if you use a recent version of WPS Office that supports dynamic array functions such as LET, SCAN, LAMBDA, and FILTER. Update WPS Office if any of these functions are not recognized.
What should I do if my Category and Score columns are not the second and third columns?
Change the {2,3} argument in CHOOSECOLS to the actual column positions in your table. For example, if Category is column 4 and Score is column 5, use {4,5}.
Can I use this formula without an Excel Table?
Yes. Replace scores[Name] with the actual Name range, and replace the FILTER input with the full range that includes the name, category, and score columns.
Why does the formula return a #SPILL error?
The spill range is probably blocked by existing data or formatting. Clear the cells below and to the right of the formula cell so the dynamic array has enough room to expand.
How are tied highest scores in the same category handled?
The formula sorts rows by Category and Score descending, then uses XLOOKUP to keep the first matching score for each category, so one highest value per category is retained when ties exist.
