Excel Consolidate Duplicates and Sum Values

How-to basics · Updated November 19, 2026

Yes. In Excel for Microsoft 365, use UNIQUE to list each label once and SUMIF to add the numbers associated with each label; for a quick summary without formulas, create a PivotTable. Choose the method that fits how often your source data changes and whether you need a reusable report.

Use UNIQUE and SUMIF for a formula summary

This method keeps the original rows intact and creates a separate summary that updates when you change the source values. It works well when each record has one grouping label, such as a product, customer, or department, and one numeric value to total.

Assume the item names are in A2:A7 and the amounts are in B2:B7. The sample rows are Pen 12, Notebook 8, Pen 5, Folder 4, Notebook 7, and Pen 3. In D2, enter the following formula to return each distinct item:

=UNIQUE(A2:A7)

With that layout, D2 spills the labels Pen, Notebook, and Folder into the cells below. In E2, enter this formula to add the amounts for the label in D2:

=SUMIF($A$2:$A$7,D2,$B$2:$B$7)

With the same layout, fill E2 down alongside the unique labels. The result is Pen 20, Notebook 15, and Folder 4. The arithmetic is 12 + 5 + 3 = 20 for Pen, and 8 + 7 = 15 for Notebook. The dollar signs keep both source ranges fixed as you copy the formula down; D2 changes to D3, D4, and so on.

UNIQUE is available in Microsoft 365 and Excel 2021 or later. If the source list grows, use a formatted Excel table or expand the ranges so new rows are included. A formula tied to A2:A7 will not automatically include a new record entered in row 8.

What “consolidate duplicates” means

In spreadsheet work, consolidating duplicates usually means grouping rows that share a key and combining their numeric values. It does not necessarily mean that every cell in two rows is identical. For instance, several sales records can have the same product but different dates and quantities; the intended result may be one total per product while preserving the fact that the original transactions were separate.

Decide what makes a record part of the same group before summarizing. It might be a single field, such as product name, or a combination such as product and month. If you group only by product, sales from every date will be combined. If the business question is monthly sales by product, the grouping key needs to include both product and month, or the data needs to be summarized at that level with a PivotTable or query.

Keep an untouched copy of the source data, especially before using a command that removes rows. A summary is an output, not a substitute for transaction-level records: you may later need to check a total, correct a typo, or report a different grouping.

Create a PivotTable for an interactive summary

A PivotTable is often the fastest option when you want to inspect totals, rearrange categories, or summarize a long list without writing formulas. It displays one group for each distinct label and aggregates the numeric field. It does not delete or merge the underlying source rows.

  1. Click a cell in the source data, which should have a separate header for each column and no completely blank rows or columns within the list.
  2. On Windows, select Insert > PivotTable, choose the table or range, and select where to place the PivotTable.
  3. In the PivotTable Fields pane, drag the grouping field, such as Product, to Rows. Drag the numeric field, such as Amount, to Values.
  4. Check that the Values area says Sum of Amount. If it says Count of Amount, open the field’s menu, choose Value Field Settings, and select Sum.

If the source is an Excel table, newly added rows are easier to include: refresh the PivotTable after adding records. Select a PivotTable cell and choose PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh. A PivotTable generally does not refresh its displayed totals just because a source value changed; refresh it to recalculate the summary.

In Excel for Mac, the same basic PivotTable approach is available, although ribbon labels and pane layout can vary slightly by release. Excel for the web can create and work with PivotTables too, but some advanced options differ from desktop Excel. The field arrangement is the same concept across versions: categories go in Rows and amounts go in Values.

Use Power Query for repeatable data cleanup

Power Query is useful when you repeatedly receive files that need the same grouping, cleanup, or combination of steps. Instead of manually rebuilding a summary, create a query that groups the records and sums a numeric column, then refresh it when the source changes.

  1. Convert the source range to a table if appropriate, then select a cell in it and choose Data > From Table/Range.
  2. In the Power Query Editor, select the column or columns that define a group.
  3. Choose Home > Group By. Select the grouping field, set a new column name such as Total Amount, choose Sum as the operation, and select the numeric column to aggregate.
  4. Choose Home > Close & Load to return the result to Excel. Refresh the query after updating its source.

For a single field, group by Product and sum Amount. For a two-part key, select both Product and Month before using Group By, so the query produces a separate total for each product-month pair. Power Query can also help standardize data before grouping—for example, trimming stray spaces or setting a column to a consistent data type. Excel for Mac has Power Query capabilities, but available connectors and authoring features vary by Excel release; check the Data tab in your version if a command is missing.

When Data Consolidate is the right tool

Excel’s Data > Consolidate command is a separate feature from removing duplicate rows. It can summarize values from multiple ranges, including ranges on different worksheets, using a function such as Sum. It is most relevant when similar reports are stored in separate ranges and you want to combine them by matching row or column labels.

Choose Data > Consolidate, select Sum as the function, add each source range, and use the label options if the ranges have matching labels in their top row or left column. Label matching matters: without it, Excel may combine data by position rather than by the meaning of the labels. The layout of the source ranges should be consistent enough to make the intended alignment clear.

For one ordinary list with repeated names, a PivotTable or a UNIQUE-and-SUMIF summary is usually simpler. Consolidate is not a general-purpose command that automatically detects every kind of repeated record, cleans inconsistent labels, or replaces the source with one merged list. Review the result against the source, particularly when ranges have different layouts.

Choose the right grouping key and check the source

Before choosing a tool, confirm that the labels Excel will compare actually match. “North Region” and “North Region ” may look alike but differ because one has a trailing space. Different spellings, abbreviations, punctuation, or numeric values stored as text can also cause a summary to split what a person considers one category. Clean or standardize the source when those distinctions are accidental.

  • One label and one amount: Use UNIQUE with SUMIF for a compact, live formula summary, or a PivotTable for a quick report.
  • Several grouping fields: Use a PivotTable with multiple fields in Rows, or Power Query Group By with multiple selected columns.
  • Repeated monthly or file-based work: Power Query can make the preparation and aggregation steps repeatable.
  • Several similarly structured ranges: Consider Data Consolidate when combining labeled ranges is the actual task.
  • Records that should be deleted, not totaled: Use Remove Duplicates only after deciding which row to keep and preserving a backup.

Also check that the values to sum are numbers. A value that looks like 12 but is stored as text may not behave as expected in calculations. In a PivotTable, an unexpected Count rather than Sum is a useful clue to inspect the source column. Blank labels deserve attention too: a blank may represent missing data that should be corrected, or a legitimate category that should remain in the summary.

Common mistakes and how to avoid them

The most important misconception is that Remove Duplicates sums repeated values. It does not. On Windows, the command is under Data > Remove Duplicates. It removes duplicate rows based on the columns you select and keeps one occurrence; it does not add the amounts together. Use it only when deleting repeated records is genuinely the goal. The command changes the data, so make a copy first.

Another common problem is grouping on too few columns. If two customers share a name, grouping only by name can combine their transactions. If a product is sold in different currencies or units, summing those values together may make no sense. Choose a key that represents the intended comparison, and include any necessary identifiers such as customer number, currency, or period.

Formula summaries can also appear incomplete when the source range excludes new rows, or when the labels contain inconsistent spaces. PivotTables can seem stale if they have not been refreshed. Power Query results can be stale for the same reason: refresh the query after changing its source. In all cases, compare a few category totals to the underlying records before relying on the report.

Frequently Asked Questions

How do I sum values with duplicate names in Excel?
List the distinct names with UNIQUE, then use SUMIF with the name as the criterion and the values as the sum range. A PivotTable is a convenient alternative when you do not need a formula-based output.

Does Remove Duplicates add the numbers together?
No. Remove Duplicates deletes repeated rows according to the columns you select; it does not calculate totals. Use SUMIF, a PivotTable, Power Query, or Data Consolidate when the objective is to aggregate values.

Can I sum duplicates based on two columns?
Yes. Use a PivotTable with both fields as row categories, or group by both columns in Power Query. With formulas, SUMIFS can apply multiple criteria, but you also need a distinct list of the combinations to summarize.

Why is my PivotTable counting instead of summing?
Excel may treat the source values as text or encounter blanks or nonnumeric entries in the field. Inspect and correct the source column, then set the field to Sum in Value Field Settings and refresh the PivotTable.

Will a formula or PivotTable update when I add rows?
A fixed formula range will not include rows outside that range. An Excel table can expand as you add records, but refresh a PivotTable after adding data. Power Query also needs a refresh to load changed source data.

Can Excel combine duplicates without deleting the original data?
Yes. A formula summary, PivotTable, or Power Query output can show grouped totals while leaving the source records available. Keep the source if you may need transaction-level detail later.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.