To sum values associated with repeated names or codes in Excel, use SUMIF: it adds the numbers in one range whenever the matching entry appears in another. For example, with product names in A2:A8 and amounts in B2:B8, a formula can total every row labeled Apples, including all duplicate Apples entries.
What “sum duplicates” can mean
The phrase “sum duplicates” can describe more than one task, so it helps to decide what you want the result to represent before choosing a formula. Often, a list contains repeated categories, customers, products, or invoice IDs, and each row has an amount. The goal is to combine the amounts for each repeated label. In that situation, you usually want a total for every label, whether it occurs once or several times.
Sometimes, however, you want to identify entries that occur more than once and add only their amounts. There is also a narrower interpretation: exclude the first occurrence and add only the later, duplicate rows. Those are different calculations. Excel does not have one built-in command called “sum duplicates” that automatically decides which meaning you intend; you need to define the matching field and the rows to include.
For the examples below, assume a worksheet has product names in A2:A8 and amounts in B2:B8:
- Apples — 4
- Pears — 3
- Apples — 6
- Apples — 2
- Pears — 5
- Oranges — 7
- Pears — 1
The total for Apples is 12, the total for Pears is 9, and the total for Oranges is 7. The full list sums to 28. These figures make it possible to check whether a formula is grouping and adding the intended rows.
Use SUMIF to total one repeated value
SUMIF is the straightforward choice when you need the total for one known label. It checks a criteria range for a match and adds the values in the corresponding positions of a sum range. The ranges should line up row by row: the first name in the criteria range corresponds to the first amount in the sum range, and so on.
With product names in A2:A8, amounts in B2:B8, and the word Apples entered in D2, use:
=SUMIF($A$2:$A$8,D2,$B$2:$B$8)
This returns 12, because it adds 4, 6, and 2 from every row whose name matches the value in D2. Change D2 to Pears and the result is 9. The dollar signs keep the source ranges fixed if you copy the formula to other cells, while D2 changes to D3, D4, and so on when copied down.
The general pattern is SUMIF(criteria_range,criteria,sum_range). The criteria range contains the values Excel evaluates; the criteria can be a word, a number, a comparison, or a reference to a cell; and the sum range contains the numbers to add. If the criteria range and sum range are different sizes, Excel can produce results that are confusing, so use corresponding ranges with the same number of rows.
Create a summary for every distinct label
If you need totals for all names rather than just one, create a compact summary. In Microsoft 365 for Windows, the UNIQUE function can return each distinct label once. It spills the results into cells below the formula, so keep that output area empty.
With product names in A2:A8, enter this in D2 to list each distinct name in first-appearance order:
=UNIQUE(A2:A8)
The result is Apples, Pears, and Oranges in D2:D4. UNIQUE is available in Microsoft 365 and Excel 2021 or later, but not in older perpetual versions such as Excel 2019. If a cell in the spill area is occupied, Excel displays a spill error; clear the obstructing cells or move the formula.
With the unique names in D2:D4 and source amounts still in B2:B8, enter the following in E2 and copy it down through E4:
=SUMIF($A$2:$A$8,D2,$B$2:$B$8)
The summary returns Apples 12, Pears 9, and Oranges 7. The formula is the same as the single-label version; its criteria reference points to the label beside each total. This pairing of a distinct-name list and SUMIF is useful for a repeatable report that updates when source amounts change. If you add rows beyond row 8, extend the source ranges or convert the data to an Excel Table so the ranges can expand with added records.
Sum only values belonging to duplicated labels
Sometimes the desired answer is one combined number for labels that appear at least twice. In the example, Apples and Pears are duplicated, while Oranges occurs once. Adding every amount for the duplicated labels gives 21: 12 for Apples plus 9 for Pears. This includes the first occurrence of each repeated label, because the test is whether that label occurs more than once anywhere in the list.
A summary table is often the clearest way to get that answer: calculate a total for each distinct label, then add only the totals for labels that recur. You can check recurrence next to the summary with a count. With product names in A2:A8 and a distinct-name list in D2:D4, enter this beside the first name, in E2, and copy down:
=COUNTIF($A$2:$A$8,D2)
The results are 3 for Apples, 3 for Pears, and 1 for Oranges. Add the corresponding totals only when the count exceeds 1. This approach makes it easy to inspect which labels qualify and is preferable when you need an auditable report rather than just a single number.
Sum the later duplicate rows, excluding first occurrences
“Only the duplicates” can also mean the repeated rows after the first appearance, leaving the original row out. This is a different rule from adding all amounts for duplicated labels. In the example, the later Apples rows contribute 6 and 2, and the later Pears rows contribute 5 and 1. The later duplicate rows therefore sum to 14.
To mark those rows, add a helper column. With product names in A2:A8, enter this formula in C2 and fill it down through C8:
=COUNTIF($A$2:A2,A2)>1
The first time a label appears, the running count is 1 and the result is FALSE. Later appearances return TRUE. For example, the second Apples row and the second and third Pears rows are marked TRUE. The formula treats the first row of the selected data as the first occurrence, so make sure the range starts at the actual beginning of the records, not at a header.
With TRUE or FALSE markers in C2:C8 and amounts in B2:B8, use this formula to add only the later rows:
=SUMIF(C2:C8,TRUE,B2:B8)
The result is 14. The helper-column method is transparent: filter column C for TRUE to see the exact rows being added. It also avoids ambiguity when a label appears three or more times, because every occurrence after the first is included.
Choose between formulas, a PivotTable, and Remove Duplicates
Use a formula when you want totals that recalculate as the source data changes. SUMIF handles one criterion, while SUMIFS is useful when a total must meet several conditions, such as a product name and a month. A summary using UNIQUE plus SUMIF is handy in a Microsoft 365 workbook when the list of labels may change.
A PivotTable is a good alternative for interactive summaries, especially when the data has several fields such as product, region, and date. Select a cell in the data, choose Insert > PivotTable, and place the product field in Rows and the amount field in Values. Excel normally summarizes numeric Values by Sum; if it shows Count instead, open the field’s value settings and choose Sum. PivotTables can be refreshed after source data changes, and their layout makes it easy to add or remove categories without writing a separate formula for each one.
Do not use Data > Remove Duplicates as a way to total repeated records. That command deletes rows from the selected data; it does not combine their amounts. If you want a cleaned list, first make a copy of the source data, or build a summary that preserves the original records. A formula-based report or PivotTable is safer when you need to retain the detail behind each total.
Common problems that change the result
If a total is unexpectedly low or zero, check that the names really match and that the amounts are numbers. A cell containing a number stored as text may not behave as expected in a sum range. Also look for leading or trailing spaces, inconsistent spellings, and extra characters such as a nonbreaking space copied from a website. For example, Apples and Apples followed by a space can look identical on screen but be treated as different text.
SUMIF is not case-sensitive, so it treats apples and Apples as the same text for a match. It does not automatically remove spaces or standardize spelling. Clean the source labels if those differences are accidental. If a formula returns an error after adding rows, verify that the ranges cover the new data and that no cells block a dynamic array result from UNIQUE.
When a formula appears as text instead of calculating, confirm that the cell is not formatted as Text and that the formula begins with an equals sign. Re-entering the formula after changing the format to General can help. Excel formulas in these examples use commas between arguments; some regional settings use semicolons instead. If Excel rejects a formula with commas, check the list-separator setting for the computer rather than changing the function name.
Frequently Asked Questions
How do I sum duplicate names in Excel?
Use SUMIF with the name column as the criteria range and the amount column as the sum range. Put the name to total in a cell and reference that cell as the criterion. To get totals for every name, make a distinct-name list and copy a SUMIF formula beside it.
Does SUMIF add every occurrence or just the duplicate rows?
It adds every matching row, including the first occurrence. If Apples appears three times, a SUMIF for Apples adds all three corresponding amounts. To exclude first occurrences and add only later repeats, use a running COUNTIF helper column and sum rows marked TRUE.
How can I total duplicates across multiple columns?
Use SUMIFS when each row must match more than one condition, such as a product and a region. Keep each criteria range aligned with the sum range. If you mean that a duplicate is defined by a combination of two columns, create a reliable combined key or use a PivotTable with both fields as row labels, then sum the amount field.
Can I sum duplicates without deleting any data?
Yes. Formulas and PivotTables calculate summaries without removing the source rows. Data > Remove Duplicates is a data-cleaning command that removes records, so it is not a summing method. Preserve a copy before using it if you need the original detail.
Why does my duplicate total differ from someone else’s?
Check whether both calculations include the first occurrence, whether they group by exactly the same fields, and whether the selected ranges include the same rows. Then inspect spaces, spelling differences, numbers stored as text, and hidden filtered rows. “All rows for repeated labels” and “only later duplicate rows” are distinct totals.
Keep the meaning of the total clear
A correct formula still needs a clearly defined rule. State whether the result combines all rows with the same label, includes only labels that repeat, or excludes each label’s first appearance. Then choose SUMIF, a distinct-label summary, or a helper column accordingly. For a one-off grouped report, a PivotTable may be quicker; for a live worksheet result, formulas are often easier to keep connected to the underlying data. Label the output so another reader can tell which interpretation the total represents.
Related
- Year Calendar in Excel: Create a 12-Month View
- When Was Excel Created? The 1985 Launch Explained
- Excel Weekday Text: Show Dates as Day Names
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Data Validation Studio: Excel Rules, Setup, and LimitsThere is no built-in Microsoft Excel feature officially named Data Validation Studio. In Excel for Microsoft 365, the…
- Google Sheets Date Range Formula: Count, Filter, and SumIn Google Sheets, use COUNTIFS to count dates within a range, SUMIFS to total matching values, or FILTER…
- How to Make a Flowchart in WordThe easiest way to make a flowchart in Word is to insert flowchart shapes, label them, and join…
- Addition in Excel PivotTables: Sum Values ClearlyTo add values in an Excel PivotTable, drag the numeric field into the Values area; Excel usually summarizes…
- Excel Will Not Respond? Fixes for a Frozen WorkbookIf Excel will not respond, first wait briefly: a large calculation, refresh, or file operation can temporarily make…
- How to Delete a Blank Page in Microsoft WordTo delete a blank page in Microsoft Word, show formatting marks, find the paragraph mark or break that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.