How to Combine Duplicate Item Numbers and Sum Weights with SUMIF in WPS Spreadsheets
Question details
The user needs to consolidate duplicate item numbers from a BULK sheet into a SUMMARY sheet and sum tag weight and actual weight for each item.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A BULK sheet has multiple rows for the same item number, and a SUMMARY sheet must show one row per item with total tag weight and actual weight.
- Observed behavior
- Duplicate item numbers repeat across rows, so totals must be aggregated by item number using SUMIF.
Make sure the BULK sheet uses consistent item numbers in one column and numeric weight columns without text or merged cells. Prepare a SUMMARY sheet with column A reserved for the unique item numbers.
Create a Unique Item List and Sum Weights with SUMIF
Use this method when you need a clear SUMMARY sheet that totals tag weight and actual weight for each item number.
SUMIF totals a range based on a matching criterion, so it can pull values from the BULK sheet for each unique item number. The formulas can be filled down to update all items at once.
Open WPS Spreadsheets and go to the SUMMARY sheet. In column A, enter each unique item number once, starting at A2.
In cell B2, enter =SUMIF(BULK!$A:$A,A2,BULK!$B:$B) and press Enter to total the tag weight for the item number in A2.
In cell C2, enter =SUMIF(BULK!$A:$A,A2,BULK!$C:$C) and press Enter to total the actual weight for the same item number.
Select B2:C2 and drag the fill handle down or double-click it to apply the SUMIF formulas to every unique item number.
Generate the Unique Item List Automatically with UNIQUE
Use this method if your WPS Spreadsheets version supports UNIQUE and you want the SUMMARY sheet to create the item list automatically.
Use SUMIF in WPS Spreadsheets to Consolidate Duplicate Item Numbers
WPS Spreadsheets can directly sum tag weight and actual weight for each item number with SUMIF formulas. Enter a unique item list, reference the BULK sheet ranges, and fill the formulas down to create a clean SUMMARY sheet.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and open the workbook that contains the BULK and SUMMARY sheets.
- 2. List unique item numbers on the SUMMARY sheet: In the SUMMARY sheet, enter each item number once in column A, starting at A2.
- 3. Enter the SUMIF formulas for both weights: In B2 and C2, enter =SUMIF(BULK!$A:$A,A2,BULK!$B:$B) and =SUMIF(BULK!$A:$A,A2,BULK!$C:$C), then press Enter.
- 4. Fill formulas down and check totals: Drag the fill handle from B2:C2 down through the unique item list and verify that the totals match the BULK sheet.
Frequently Asked Questions
Can SUMIF reference a different sheet in WPS Spreadsheets?
Yes. Use the sheet name followed by an exclamation mark, such as =SUMIF(BULK!$A:$A,A2,BULK!$B:$B), to sum values from another sheet.
What do I do if the weight values are not in columns B and C?
Change the sum_range in the SUMIF formula to the correct column. For example, if actual weight is in column D, use =SUMIF(BULK!$A:$A,A2,BULK!$D:$D).
How can I create a unique item number list without Microsoft 365 UNIQUE?
Copy the item numbers to the SUMMARY sheet, select the list, and use Data > Remove Duplicates in WPS Spreadsheets to keep one row per item. You can also type the unique item numbers manually.
Why does my SUMIF formula return 0?
Check that the item numbers in both sheets match exactly, with no extra spaces or different formatting, and confirm that the weight cells are formatted as numbers rather than text.
Does WPS Spreadsheets support the UNIQUE function?
Recent WPS Spreadsheets versions support UNIQUE. If your version does not, use the Remove Duplicates command or enter the unique item numbers manually.
