Yes. To combine repeated labels and add their associated numbers in Excel, use a PivotTable, SUMIF, or Power Query; the Remove Duplicates command alone deletes repeated records and does not add their values. For a quick, refreshable summary, a PivotTable is often easiest, while formulas are useful when you want the result to update directly on the worksheet.
What removing duplicates and summing actually means
Consider a sales list with a product in column A and a quantity in column B. If Apples appears three times with quantities 5, 7, and 1, the goal is usually one Apples row with a total of 13. That is aggregation: group rows by a chosen field, then calculate a total for each group. It is different from deleting duplicate rows.
The distinction matters because repeated labels do not necessarily represent duplicate records. Three Apples entries may be three separate sales that should all contribute to the total. If you use Data > Remove Duplicates and select only the product column, Excel keeps one Apples row and discards the other matching rows, including their quantities. The result is not a total; it is lost data.
Before choosing a method, decide what makes two records the same. It might be a product, customer, invoice number, or a combination such as customer and month. Also decide whether the output should be a one-time cleaned list, a live worksheet summary, or a report that can be refreshed when source data changes.
Use a PivotTable for a quick total by label
A PivotTable is usually the simplest way to summarize a list without writing formulas. It groups matching entries and totals a numeric field, while leaving the source rows intact. In Excel for Microsoft 365 on Windows, first make sure the source has a header row, such as Product and Quantity, with no completely blank rows or columns inside the data.
- Click a cell in the source data, then choose Insert > PivotTable.
- Confirm the table or range and choose where to place the PivotTable. Select New Worksheet for a separate report.
- In the PivotTable Fields pane, drag Product to Rows and Quantity to Values.
- Check that the Values field says Sum of Quantity. If it says Count of Quantity, open its menu, choose Value Field Settings, select Sum, and confirm.
For the example list, the PivotTable would show Apples 13, Oranges 5, and Pears 10. It creates one displayed row per product without removing any source records. If you add or change source data, refresh the PivotTable using PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh. If the source is an Excel Table, new rows added to that table are easier to include in the PivotTable source, though you still need to refresh the report.
PivotTables can also group by more than one field. Put Region above Product in Rows to see products within regions, or put a date field in Rows and group dates by month if that is the reporting level you need. The exact layout depends on which fields you place in Rows, Columns, Filters, and Values.
Use SUMIF with a unique list
SUMIF is a good choice when you want a compact summary beside your data or need totals to recalculate as the source values change. Suppose rows 2 through 8 contain these pairs: Apples 5, Oranges 3, Apples 7, Pears 4, Oranges 2, Apples 1, and Pears 6. The totals should be Apples 13, Oranges 5, and Pears 10.
With product names in A2:A8 and quantities in B2:B8, enter the following in D2 to generate a sorted list of distinct product names:
=SORT(UNIQUE(A2:A8))
With the same source layout and the unique names spilling from D2, enter this in E2 to return a matching total for each name:
=SUMIF($A$2:$A$8,D2#,$B$2:$B$8)
The # after D2 refers to the full spilled results from the formula in D2. In Microsoft 365, the SUMIF result spills down alongside the unique names. For this example, the resulting pairs are Apples 13, Oranges 5, and Pears 10. SORT and UNIQUE are available in Microsoft 365 and Excel 2021; this approach may not work in older perpetual versions that lack dynamic arrays.
If your Excel version does not support spill formulas, create a distinct list in D2:D4 using a suitable method, then put this formula in E2 and fill it down through E4. With product names in A2:A8, quantities in B2:B8, and one product per cell in D2:D4, use:
=SUMIF($A$2:$A$8,D2,$B$2:$B$8)
This copied-down version calculates each total independently. The dollar signs keep the source ranges fixed as you fill the formula, while D2 changes to D3 and D4. You can create the distinct list by copying the product column elsewhere and using Data > Remove Duplicates on that copied list, not on the original records.
When records should be unique across multiple columns
Sometimes one field is not enough to define a group. For example, a salesperson may sell the same product in several regions. Summing by product alone combines all regions; if the intended output is one row per product and region, include both fields in the PivotTable Rows area.
With formulas, SUMIFS can total using multiple criteria. For example, assume Region is in A2:A8, Product is in B2:B8, and Sales is in C2:C8. Put a region in E2 and a product in F2; this formula returns the total for that particular combination:
=SUMIFS($C$2:$C$8,$A$2:$A$8,E2,$B$2:$B$8,F2)
Here the sum range is Sales, and the two criteria ranges test Region and Product. You need a distinct list of the combinations you want to report, or can create the report with a PivotTable. This is also why it helps to state the definition of a duplicate before cleaning a dataset: same product is a different rule from same product in the same region.
Use Remove Duplicates only to delete repeated records
Use the built-in command when you truly want to keep one record and discard the other rows that match selected fields. Work on a copy if the original rows may be needed later. Select the data, choose Data > Remove Duplicates, confirm whether the data has headers, and check the columns Excel should compare. Selecting every column identifies rows that match across all selected fields; selecting only Product treats every repeated product as a duplicate, even if its quantity differs.
Excel retains the first matching row it encounters and removes later matches. It does not combine numeric values, average them, or choose the row with the largest value. Sorting the source first can affect which record is retained, so sort deliberately if the kept record matters. For instance, to retain the latest customer record, sort by date in descending order before removing duplicates based on customer ID, and verify that this is the intended rule.
The command changes the selected data directly and provides a count of removed and remaining values afterward. It is not the same as a formula-based summary or PivotTable. On Windows, Ctrl+Z can undo the removal if no intervening action prevents it; on Mac, use Command+Z. Still, a backup or copied sheet is safer for important data. In Excel for the web and Mac, ribbon availability and layout can differ slightly, but look under the Data tab for Remove Duplicates.
Use Power Query for repeatable data preparation
Power Query is useful when the same grouping and cleanup steps must be repeated on a refreshed dataset, especially when files are large or come from external sources. It creates a transformation workflow rather than editing the original rows in place. In Windows Excel for Microsoft 365, convert the source range to a table with Insert > Table if it is not already one, then select a cell in it and choose Data > From Table/Range.
In Power Query Editor, choose Home > Group By. Select the field to group on, choose a name for the new total column, select Sum as the operation, and choose the numeric column to sum. Use Advanced in the Group By dialog if you need to group by multiple fields, such as Product and Region. Confirm the settings, then select Home > Close & Load to return the summarized results to Excel.
Power Query is a strong option for recurring work because you can refresh the query after the source table changes. It is not an instant formula recalculation: the output updates when the query is refreshed. Power Query capabilities and connectors vary by Excel platform and version; if a command is unavailable in your edition, a PivotTable or SUMIF may be a more portable choice. Treat the loaded summary as query output and make edits in the source or query steps, rather than assuming changes made directly in the output will persist after refresh.
Choose the method that fits the job
- PivotTable: Choose this for a fast, interactive report, totals across several fields, or simple comparisons. It preserves the source and makes it easy to rearrange the grouping.
- SUMIF or SUMIFS: Choose these when totals must sit in a specific worksheet layout, feed other formulas, or update as values change. You must maintain the summary labels and formula ranges appropriately.
- Power Query: Choose this when you repeat the same import, cleanup, and grouping process. It is designed for refreshable transformations, not immediate recalculation after every cell edit.
- Remove Duplicates: Choose this only when repeated records should actually be deleted and no aggregation is required. It is a data-cleaning command, not a summing tool.
All four approaches can be useful, but they produce different outcomes. If the goal is to report totals, preserve the underlying transactions and make a separate summary. If the goal is to remove redundant copies of a record, first define exactly which columns determine a duplicate and make a backup.
Common problems and checks
A PivotTable counts instead of sums. The source values may be stored as text, or the field setting may be Count. Convert text numbers to numeric values and check Value Field Settings. A quick visual clue is that numbers often align differently from text by default, though formatting can override that clue.
Labels that look identical are treated as separate items. Leading or trailing spaces, nonprinting characters, and inconsistent spelling can split a group. For example, Apples and Apples followed by a space may appear alike but be different text. Clean the source consistently before summarizing; TRIM can remove ordinary extra spaces, while unusual nonbreaking spaces may need separate cleanup.
Totals seem too high or too low. Check whether the chosen grouping fields match the question, whether all source rows are included, and whether quantity or sales values are numeric. In a PivotTable, inspect filters and the source range. In formulas, confirm the ranges cover every data row and that each unique label appears once in the summary list.
Blank labels appear. If the source range includes empty cells, UNIQUE may return a blank item and a PivotTable may show a blank category. Exclude empty source rows or filter blank labels where appropriate. Avoid selecting entire columns in a formula when a bounded range or Excel Table is practical, because unrelated content lower down the sheet can then be included.
One value is lost after removing duplicates. This usually happens when Remove Duplicates was run on the original list using only a label column. Undo if possible, restore the source from a copy, and use a PivotTable or SUMIF to aggregate instead. The command cannot reconstruct values from rows it has deleted.
Frequently Asked Questions
Can Excel remove duplicates and add the values automatically?
Not with the Remove Duplicates command itself. Use a PivotTable, SUMIF or SUMIFS, or Power Query Group By to create one row per group with a total.
Does Remove Duplicates keep the first or last row?
It keeps the first occurrence in the current row order and removes later matching rows. Sort the data first if the record retained should follow a particular rule, and preserve a copy before deleting rows.
How do I sum duplicates based on two columns?
Put both fields in a PivotTable’s Rows area, or use SUMIFS with one criteria range for each field. This creates totals for each combination instead of merging all rows that share just one field.
Will SUMIF update when I add new rows?
It updates when values inside its referenced ranges change, but a fixed range such as A2:A8 will not automatically include a new row 9. Use an Excel Table with structured references or adjust the ranges; a PivotTable also needs refreshing after source changes.
Can I keep the original data and still remove duplicates?
Yes. Copy the data to another sheet or workbook first, or create a separate summary with a PivotTable, formulas, or Power Query. A separate summary is the safer approach when repeated rows represent separate transactions that must be totaled.
Related
- Excel Question Solver AI: How to Get Reliable Answers
- Excel Quadratic Equation Solver: A Practical Guide
- Excel Protected Sheet Unlock Online Free: Safe Methods
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Insert Row Greyed Out? Causes and FixesIn Excel for Microsoft 365 on Windows, Insert Row is usually greyed out because the worksheet is protected,…
- Excel Open Corrupted File? Recovery Steps That WorkTo open a corrupted workbook in Excel for Microsoft 365 on Windows, make a copy of the file,…
- How to Make a Work Schedule in Google Sheets: A GuideTo make a work schedule in Google Sheets, create a table with one shift per row, add columns…
- ChatGPT Excel Data Analysis: How to Get Useful ResultsYes. ChatGPT can analyze Excel data when you upload a supported workbook, such as an .xlsx file, or…
- PowerPoint: How to Animate Part of TextYes. PowerPoint can reveal text by paragraph, word, or letter, but if you want one specific phrase to…
- Car Loan Amortization Schedule in Google SheetsYes. In Google Sheets, enter the amount financed, annual interest rate, and loan term, calculate the monthly payment…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.