Pivot Table Issues

Sum Excel Quantities by Code and Flag Mismatched Units

Maira MehtabMaira Mehtab Nov 20, 2026 868 views

Question details

Sum quantities for repeated item codes only when their units match, and flag codes with conflicting units for review.

Sum Excel Quantities by Code and Flag Mismatched Units
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.
Before you start

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.

Solution 1Recommended

Create a PivotTable grouped by code and unit

Use a PivotTable to sum each code and unit combination and make conflicting units visible.

1
Select the data

Select the range containing the Code, Quantity, and Unit headers and all data rows.

2
Insert a PivotTable

Select Insert > PivotTable, choose where to place it, and confirm the selection.

3
Add the row fields

In the PivotTable Fields pane, drag Code to Rows, then drag Unit below Code in Rows.

4
Sum the quantities

Drag Quantity to Values. If it shows Count instead of Sum, open Value Field Settings and select Sum.

5
Review conflicting units

Inspect codes with more than one unit listed beneath them. Correct the source data if needed, then right-click the PivotTable and select Refresh.

Create a PivotTable grouped by code and unit
Review code subtotals: A code subtotal can combine quantities across different units. For a code with conflicting units, use the separate unit-level totals and disable subtotals if they would be misleading.
Summarize data in WPS Spreadsheets

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. 1. Open the workbook: Open your Excel file in WPS Spreadsheets and select the data range, including the Code, Quantity, and Unit headers.
  2. 2. Create a PivotTable: Select Insert > PivotTable, confirm the source range, and choose where to place the table.
  3. 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. 4. Check the units: Review codes with multiple unit rows. Correct any source entries as needed and refresh the PivotTable.
See separate totals for each code and unit combinationIdentify codes that appear with more than one unitOpen and save Microsoft Excel XLS and XLSX filesUse a familiar spreadsheet interface
microsoft office alternative - wps office

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.