How to Combine Duplicate Rows and Sum Columns in Excel
Question details
The user needs a method to combine records with matching identifiers, sum their corresponding numeric columns, and retain other descriptive fields.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning up and summarizing datasets that contain duplicate identifiers with associated numeric and descriptive data.
- Observed behavior
- Duplicate rows exist in the dataset, and the goal is to merge them into single rows per identifier while calculating the total for specific numeric values.
Ensure your dataset has clear column headers and no blank rows. It is also highly recommended to create a copy of your original data before applying complex formulas or SQL queries.
Use an SQL Query to Group and Sum Data
Ideal for users who manage data through database connections or Power Query, allowing for robust grouping and aggregating.
If you are querying a database or using a SQL interface within your spreadsheet environment, you can use standard SQL clauses to group duplicates and sum the metrics.
Write a SELECT statement to choose your identifier and descriptive columns (e.g., f01, f04, f05).
Use the SUM() function for the numeric columns you want to total, such as SUM(f02) and SUM(f03).
Add the GROUP BY clause at the end of your query to group by the identifier and descriptive columns. For example: SELECT f01, SUM(f02), SUM(f03), f04, f05 FROM sheet1 GROUP BY f01, f04, f05.
Create a Custom LAMBDA Function
A dynamic array formula approach for advanced users who want a reusable function to summarize data automatically.
Easily Combine and Sum Data with WPS Spreadsheet
Instead of writing complex SQL queries or advanced LAMBDA functions, you can quickly consolidate duplicate rows and sum columns using the intuitive Pivot Table and Data Consolidate features built right into WPS Spreadsheet.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your duplicate rows.
- 2. Insert a Pivot Table: Highlight your data range, navigate to the Insert tab, and click PivotTable.
- 3. Set up your rows: Drag your unique identifier and descriptive fields into the 'Rows' area of the PivotTable Field List.
- 4. Sum the numeric values: Drag the numeric columns into the 'Values' area. WPS Spreadsheet will automatically sum the data for each unique identifier.

Frequently Asked Questions
Can I combine duplicate rows without using formulas?
Yes, the easiest way to combine duplicates and sum values without formulas is by inserting a Pivot Table or using the 'Consolidate' tool located under the Data tab.
Why is my SQL query returning an error when summing columns?
Ensure that all non-aggregated columns (identifiers and descriptive fields) included in your SELECT statement are also explicitly listed in your GROUP BY clause.
Is the LAMBDA function available in all versions of Excel?
No, the LAMBDA function is only available to Microsoft 365 subscribers. Users on older standalone versions (like 2019 or 2016) should use Pivot Tables or Power Query instead.
How does the Data Consolidate feature work?
The Consolidate feature allows you to select ranges of data and apply a specific function, such as SUM, to combine data based on matching labels in the top row or left column.





