Function Problems

How to Consolidate Duplicate Names and Sum Data in Excel

Phi Hung Vo Oct 14, 2026 871 views

Question details

Combine rows that contain duplicate names into a single summary row and calculate the total sum of their associated numeric values.

How to Consolidate Duplicate Names and Sum Data in Excel

Product: Excel
Device & OS: not provided
Scenario: Managing large lists with repeating entries and summarizing the associated numerical data into a clean, unique list.
Observed behavior: Needs a method to automatically extract unique names from a dataset and sum the corresponding numeric columns for each name.

Before you start

Ensure your dataset is organized in clean columns with clear headers, and identify the exact column containing the duplicate names you want to consolidate.

Solution 1Recommended

Consolidate Data Using UNIQUE and SUMIF Functions

Extract unique names into a new column dynamically, and use the SUMIF function to calculate the total values corresponding to each unique name.

This formula-based approach is ideal for keeping your summarized data continuously updated if the original dataset changes.

Generate a unique list of names

Select a blank cell (e.g., H2) where you want the consolidated list of names to appear. Enter the formula =UNIQUE(A2:A8), assuming your original names are in cells A2 to A8, and press Enter.

Apply the SUMIF formula

In the adjacent cell (e.g., I2) next to your first unique name, enter the formula =SUMIF($A$2:$A$8, $H2, B$2:B$8). This checks the original name range, matches it to the unique name, and sums the corresponding values in column B.

Copy the formula for all rows

Select cell I2, click and hold the small square at the bottom-right corner (fill handle), and drag it down to apply the SUMIF calculation to the rest of the unique names.

Calculate across multiple columns

If you have multiple numeric columns to sum, simply drag the fill handle horizontally across the required columns. Ensure your relative and absolute references are set up correctly before dragging.

Consolidate Data Using UNIQUE and SUMIF Functions

Understanding Absolute References: Using dollar signs ($) in the SUMIF formula (like $A$2:$A$8) locks the original data range so it doesn't shift when you copy the formula to other cells.

Solution 2

Consolidate and Sum Data Using a Pivot Table

Use a Pivot Table to quickly summarize, group, and sum data without writing complex formulas.

Efficient Data Management

Easily Consolidate and Sum Data with WPS Spreadsheet

WPS Spreadsheet fully supports dynamic arrays like the UNIQUE function and advanced formulas like SUMIF. You can quickly consolidate rows and calculate sums without hassle, using the exact same methods you would in Microsoft Excel.

1. Open your data file: Launch WPS Spreadsheet and open the document containing your duplicate records.
2. Use the UNIQUE function: Type =UNIQUE(range) in a blank cell to extract your unique names instantly.
3. Tally your values with SUMIF: Use =SUMIF(criteria_range, criteria, sum_range) in the adjacent column to sum your data based on the unique names.
4. Apply to the entire list: Drag the corner handle of the cell downwards to apply the SUMIF formula to all consolidated names.

100% compatibility with Microsoft Excel formulas, functions, and file formats.Built-in dynamic array support for rapid data extraction using UNIQUE.Intuitive PivotTable features for fast and accurate data summarization.Free, lightweight, and easy-to-use spreadsheet interface for seamless productivity.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the UNIQUE function return a #NAME? error in my spreadsheet?

The #NAME? error usually occurs if you are using an older version of Excel or a spreadsheet program that does not support dynamic array functions. Ensure your software is updated to a modern version, such as Microsoft 365 or the latest WPS Office, which fully supports the UNIQUE function.

Can I consolidate data using the SUMIFS function instead of SUMIF?

Yes. SUMIFS allows you to sum data based on multiple criteria. If you only have one condition (matching the name), SUMIF is sufficient, but SUMIFS will also work perfectly. Just remember that in SUMIFS, the sum_range is the first argument, whereas in SUMIF it is the last argument.

Is there a built-in tool to consolidate data without formulas or PivotTables?

Yes, you can use the 'Consolidate' feature. Highlight an empty destination cell, go to the 'Data' tab, and select 'Consolidate'. Choose 'Sum' as the function, add your data range, and check 'Left column' to group by the names. This will create a static summary table.

How do I sum multiple numeric columns at once for the unique names?

When writing your SUMIF formula, ensure you use mixed references for your sum range (like B$2:B$8). You can then drag the formula horizontally across your worksheet columns to automatically sum other corresponding data columns for each unique name.

Excel & Data Problems