How to Exclude Duplicate Values from an Excel SUMIFS Calculation in WPS Office
Question details
The user needs to avoid summing duplicate matching rows in a SUMIFS calculation and instead return the single percentage associated with an employee name and 403B code.

- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Calculating a percentage associated with an employee and 403B code from a table that contains duplicate records.
- Observed behavior
- SUMIFS adds every matching record, so duplicate percentage values are summed and produce an incorrect total instead of returning the employee's associated percentage.
Check whether an employee can have multiple different percentage rates for the same 403B code. This determines whether you should use a lookup, add a unique-value formula, or define a rule for handling duplicates.
Use a lookup formula to return the percentage for an employee and 403B code
Use this when you want the percentage associated with a specific employee and code rather than the total of duplicate rows.
SUMIFS is built to add all rows that match the criteria, so it cannot skip duplicates by itself. A lookup formula matches the employee and 403B code and returns one associated percentage instead of summing.
Open WPS Office Spreadsheets and load the workbook that contains the employee, 403B code, and percentage columns.
In an empty cell, enter a formula such as =LOOKUP(2,1/(($A$2:$A$100=G2)*($B$2:$B$100="403B")),$C$2:$C$100). Replace the ranges and criteria with your actual employee, code, and percentage columns.
Press Enter and check that the formula returns the single percentage rate for the employee and code instead of the sum of duplicate rows.

Audit duplicate rows with UNIQUE or a COUNTIFS helper column
Use this when you need to check whether the same employee and 403B code can have more than one rate or to decide which duplicate value should be returned.
Retrieve the correct percentage in WPS Office Spreadsheets without summing duplicates
Use WPS Office Spreadsheets to replace duplicate-summing SUMIFS formulas with a lookup. It supports Excel-compatible formulas, so you can return the employee/code percentage directly while keeping workbook formatting and compatibility.
- 1. Open your Excel workbook in WPS Spreadsheets: Download and install WPS Office, then open the .xlsx file that contains the SUMIFS duplicate calculation.
- 2. Identify the criteria and result columns: Go to the worksheet and locate the employee, 403B code, and percentage columns referenced by the current SUMIFS formula.
- 3. Replace SUMIFS with a lookup formula: In the target cell, enter a lookup that matches both employee and 403B code, such as =LOOKUP(2,1/(($A$2:$A$100=G2)*($B$2:$B$100="403B")),$C$2:$C$100).
- 4. Verify the unique returned value: Press Enter and compare the result to the source data so only one percentage is returned, not the duplicate total.
- 5. Save the workbook as .xlsx: Save the file in WPS Spreadsheets as a .xlsx workbook to keep it compatible with Microsoft Excel.

Frequently Asked Questions
Why does my SUMIFS formula add duplicate percentage values?
SUMIFS is designed to add all rows that match the criteria. If the same employee and 403B code appear more than once, it adds each matching percentage, producing a total instead of a single rate.
Can I use UNIQUE to exclude duplicates before SUMIFS?
You can use UNIQUE if your WPS edition supports it, but first confirm whether an employee can have multiple rates for the same 403B code. If the combination is unique, use a lookup formula; if not, define which duplicate value to keep.
Which lookup formula can replace SUMIFS for two conditions?
Use LOOKUP or INDEX/MATCH with multiple criteria. For example, =LOOKUP(2,1/(($A$2:$A$100=G2)*($B$2:$B$100="403B")),$C$2:$C$100) returns a percentage matching the employee and code. Adjust the ranges and criteria to your actual sheet.
What should I do if the same employee has different percentages for the same 403B code?
Decide the expected result first: keep the first, last, maximum, or minimum value, or flag the data for review. Add a COUNTIFS helper column to identify those conflicting rows before writing the final formula.
Does WPS Office support these Excel lookup and duplicate-check formulas?
WPS Office Spreadsheets supports common Excel-compatible formulas such as SUMIFS, LOOKUP, INDEX/MATCH, and COUNTIFS. Newer dynamic array functions like UNIQUE may depend on your WPS version, so test them or use a helper-column method instead.




