Pivot Table Issues

How to Combine Duplicate Rows and Sum Quantities in Excel

Kushani Nimanthika Sep 28, 2026 869 views

Question details

The user needs to group duplicate entries in an Excel spreadsheet and calculate the total sum of their corresponding quantities.

How to Combine Duplicate Rows and Sum Quantities in Excel

Product: Excel
Device & OS: not provided
Scenario: Consolidating repetitive data entries to calculate accurate total quantities for each unique item.
Observed behavior: Duplicate rows need to be merged into single unique entries with their respective quantity values accurately summed up.

Before you start

Ensure your dataset has clear column headers and contains no empty rows or merged cells within the data range you intend to consolidate.

Solution 1Recommended

Use a PivotTable to Combine and Sum Duplicates

Creating a PivotTable is the most efficient and dynamic way to group duplicate rows and automatically sum their quantities without altering the original dataset.

A PivotTable allows you to quickly summarize large datasets. By placing your duplicate criteria in rows and quantities in values, Excel automatically consolidates the information.

Select your source data

Highlight the entire range of your data, making sure to include the column headers at the top of your dataset.

Insert a PivotTable

Navigate to the 'Insert' tab on the top ribbon and click on 'PivotTable'. Choose whether you want to place it in a New Worksheet or an Existing Worksheet, then click 'OK'.

Set up the Rows area

In the PivotTable Fields pane on the right side of your screen, drag the field containing the duplicate items (e.g., Product Name or ID) into the 'Rows' area.

Sum the Quantities

Drag the field containing the quantities into the 'Values' area. Excel should automatically set the calculation to 'Sum of [Field Name]'. If it defaults to 'Count', click the field, select 'Value Field Settings', and change it to 'Sum'.

Use a PivotTable to Combine and Sum Duplicates

Dynamic Updates: If you add new data to your original table later, you can easily update the PivotTable by right-clicking anywhere inside it and selecting 'Refresh'.

Solution 2

Use the Consolidate Feature

The Consolidate tool is useful if you want to statically merge duplicates and sum quantities across one or multiple worksheets into a new fixed table.

Efficient Data Analysis

Combine Rows and Sum Data Easily with WPS Spreadsheet

WPS Office offers a powerful, user-friendly Spreadsheet application that handles PivotTables seamlessly. You can easily group duplicates and sum quantities using the exact same workflow as Microsoft Excel, entirely for free.

1. Open your file in WPS: Launch WPS Office and open your spreadsheet document containing the duplicate entries.
2. Insert a PivotTable: Select your entire dataset, go to the 'Insert' tab on the top ribbon, and click 'PivotTable'.
3. Configure your data fields: In the side pane, drag your duplicate item names to the 'Rows' field and your quantities to the 'Values' field to instantly calculate the total sum.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) files and formatting.Advanced PivotTable features to quickly summarize and group duplicate rows.Lightweight software with a familiar interface that requires zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my PivotTable counting instead of summing the quantities?

This usually happens if there are blank cells or text values in your quantity column. To fix this, click the field in the 'Values' area of the PivotTable pane, select 'Value Field Settings', and manually change the calculation from 'Count' to 'Sum'.

Can I combine duplicate rows using an Excel formula instead of a PivotTable?

Yes, you can use the UNIQUE function to extract unique values into a new column, and then use the SUMIFS function next to it to calculate the total quantity for each of those unique items.

Will combining duplicate rows delete my original data?

No. Creating a PivotTable or using the Consolidate feature extracts and summarizes the data in a new location, leaving your original source dataset completely intact and unmodified.

Excel & Data Problems