Function Problems

How to Exclude Duplicate Values from an Excel SUMIFS Calculation in WPS Office

Adam DavisAdam Davis Nov 13, 2026 868 views

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.

How to Exclude Duplicate Values from an Excel SUMIFS Calculation in WPS Office
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the workbook in WPS Spreadsheets

Open WPS Office Spreadsheets and load the workbook that contains the employee, 403B code, and percentage columns.

2
Enter a lookup formula for two criteria

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.

3
Confirm the returned percentage

Press Enter and check that the formula returns the single percentage rate for the employee and code instead of the sum of duplicate rows.

Use a lookup formula to return the percentage for an employee and 403B code
Duplicate total prevented: When the employee and code combination is unique, the lookup returns the expected percentage without adding duplicate rows.

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. 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. 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. 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. 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. 5. Save the workbook as .xlsx: Save the file in WPS Spreadsheets as a .xlsx workbook to keep it compatible with Microsoft Excel.
Supports Microsoft Excel formulas such as SUMIFS, LOOKUP, and COUNTIFS in .xlsx workbooks.Lets you return one employee/code percentage instead of summing duplicate rows.Provides a free, lightweight Office suite with a familiar Excel-like interface.Opens and saves Excel files without changing formulas, formatting, or layout.
QA img-9

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.