Sum Excel Quantities by Code and Flag Mismatched Units
Question details
Sum quantities for repeated item codes only when their units match, and flag codes with conflicting units for review.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains repeated item codes, quantities, and units that need to be summarized without combining incompatible units.
- Observed behavior
- Each code and unit combination should have its own total, and codes associated with multiple units should be identified for review.
Arrange Code, Quantity, and Unit in separate columns with headers, and make sure quantities are numeric. Standardize unit labels and check for blank units before summarizing.
Create a PivotTable grouped by code and unit
Use a PivotTable to sum each code and unit combination and make conflicting units visible.
Select the range containing the Code, Quantity, and Unit headers and all data rows.
Select Insert > PivotTable, choose where to place it, and confirm the selection.
In the PivotTable Fields pane, drag Code to Rows, then drag Unit below Code in Rows.
Drag Quantity to Values. If it shows Count instead of Sum, open Value Field Settings and select Sum.
Inspect codes with more than one unit listed beneath them. Correct the source data if needed, then right-click the PivotTable and select Refresh.

Use SUMIFS and COUNTIFS to total and flag units
Use formulas when you want a matching-unit total and an explicit conflict flag beside each source row.
Group quantities by code and unit in WPS Spreadsheets
Use a PivotTable in WPS Spreadsheets to total quantities separately for each item code and unit, then review codes with multiple units.
- 1. Open the workbook: Open your Excel file in WPS Spreadsheets and select the data range, including the Code, Quantity, and Unit headers.
- 2. Create a PivotTable: Select Insert > PivotTable, confirm the source range, and choose where to place the table.
- 3. Arrange the fields: Drag Code and Unit to Rows, with Unit below Code, and drag Quantity to Values. Set the value calculation to Sum if necessary.
- 4. Check the units: Review codes with multiple unit rows. Correct any source entries as needed and refresh the PivotTable.

Frequently Asked Questions
Why does my PivotTable count quantities instead of summing them?
Quantity values may be stored as text or contain nonnumeric entries. Correct the source column, refresh the PivotTable, and select Sum in Value Field Settings.
How can I tell which codes have different units?
Place Code and then Unit in the PivotTable Rows area. A code with multiple unit entries needs review; alternatively, use the COUNTIFS flag described above.
Should I use a PivotTable subtotal when a code has multiple units?
No. The code subtotal can combine quantities measured in different units. Review the separate unit-level totals and disable subtotals if needed.
Will the PivotTable update after I correct a unit?
After editing the source data, right-click the PivotTable and select Refresh to update its groups and totals.




