How to Sum Values by Document and Supplier in WPS Office Excel
Question details
The user needs to group product units by the unique Document and Supplier combination and calculate the total for each group without repeating the total on every matching row.
- Product
- WPS Office Spreadsheets (Excel-compatible)
- Device & OS
- not provided
- Scenario
- A table contains Document, Supplier, and Units columns; the user wants subtotals by each Document-Supplier pair using Excel formulas or a PivotTable.
- Observed behavior
- Repeated Document and Supplier rows can cause direct SUM formulas to duplicate values; the goal is to return each unique group total once.
Confirm your data has Document, Supplier, and Units columns and note their column letters, because the formula references B, C, and E in this example and may need to be adjusted.
Use COUNTIFS and SUMIFS to total each Document-Supplier pair
Use this formula method when you want the group total to appear only beside the first occurrence of each Document-Supplier combination.
The COUNTIFS part checks whether the current row is the first occurrence of a Document-Supplier pair. SUMIFS then adds all Units rows that match that same pair. The formula returns a blank for duplicate rows, preventing double counting.
Click the cell where the first group total should appear, such as F5.
Type =IF(COUNTIFS(B$4:B5,B5,$C$4:$C5,C5)=1,SUMIFS(E$4:E$1000,B$4:B$1000,B5,$C$4:$C$1000,C5),"") and press Enter.
Drag the fill handle down from F5 through the remaining rows to calculate group totals for all Document-Supplier combinations.
Create a PivotTable to sum values by Document and Supplier
Use this method when you prefer an automatic, interactive summary table without writing formulas.
Sum Values by Document and Supplier in WPS Office Spreadsheets
You can complete the task directly in WPS Office Spreadsheets. Use COUNTIFS with SUMIFS or build a PivotTable to group and total units by Document and Supplier, and keep formulas and formatting fully compatible with Microsoft Excel workbooks.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office and open the Excel workbook that has the Document, Supplier, and Units columns.
- 2. Insert the formula output column: Click F5, enter the COUNTIFS and SUMIFS formula, and press Enter.
- 3. Fill the formula down: Drag the fill handle from F5 down through the last row to show each unique group total.
- 4. Save as Excel workbook: Save the file as an .xlsx document to preserve formulas and compatibility.
Frequently Asked Questions
Why does my SUMIFS formula show a total on every Document and Supplier row?
Because SUMIFS calculates the same group total for every matching row. Wrap it in IF with COUNTIFS equal to 1 so only the first occurrence of each combination displays the total and duplicate rows return blank.
Can I use a PivotTable instead of writing formulas?
Yes. Click inside the data, go to Insert > PivotTable, and place Document and Supplier in Rows and Units in Values to create a grouped total automatically.
Do I need to adjust the formula if my columns are not B, C, and E?
Yes. Replace B, C, and E references with the actual column letters of your Document, Supplier, and Units columns, and extend the range E$4:E$1000 as needed.
How do I create a separate unique list of Document-Supplier combinations?
You can copy the Document and Supplier columns, use Data > Remove Duplicates, and then add SUMIFS formulas beside the unique pairs to return each group total.
