Take a Guess to Win Windows 10 Pro Key!
previous arrow
next arrow
Slider

How to Merge Duplicate Cells and Calculate the Summation in Excel

Date:2019-7-29 Author:Cordelia

In actual statistics, a person or an item could appear multiple times in the list with different values followed. We can’t simply delete any of them. Instead, we need to combine these cells and sum their values. So how can we achieve it?

Look at the table below. It’s apparently that there’re 2 person show up twice in the list. I can remove one of them and calculate the summation directly but what if the list is much longer and the duplicates is a lot more?

How to Merge Duplicate Cells and Calculate the Summation in Excel

Taking my case as an example. First, copy the content in Column A to Column C. You can do it by clicking the Column Header A, pressing Ctrl+C, and clicking Column Header C or the cell C1 then pressing Ctrl+V.

How to Merge Duplicate Cells and Calculate the Summation in Excel

Keep selecting the content in Column C and click Remove Duplicates in Data tab.

How to Merge Duplicate Cells and Calculate the Summation in Excel

Select Continue with the current selection and hit Remove Duplicates…

How to Merge Duplicate Cells and Calculate the Summation in Excel

Check My data has headers since Column C contain a header “Name”. Click OK to continue.

How to Merge Duplicate Cells and Calculate the Summation in Excel

Now the duplicates have been removed in Column C.

How to Merge Duplicate Cells and Calculate the Summation in Excel

To sum the values, double-click D2 and input the formula “=SUMIF(A$2:A$14,C2,B$2:B$14)“, which refers to calculating the summation value of C2 according to the data in B$2:B$14 corresponding to the names in the range of A$2:A$14. You can adjust the formula to adapt your situation.

How to Merge Duplicate Cells and Calculate the Summation in Excel

Then put the cursor at the lower right corner of D2 till it becomes a small black cross. Drag it to select all the cells in Column D (in my case it’s from D2 to D12). Thus the formula in D2 will be applied in all the selected cells.

How to Merge Duplicate Cells and Calculate the Summation in Excel

You’ve got the result of the summation of all the people in the list without any duplicate.

Copyright Statement: Regarding all of the posts by this website, any copy or use shall get the written permission or authorization from Myofficetricks.

1 thought on “How to Merge Duplicate Cells and Calculate the Summation in Excel”

  1. asdfsadf says:

    FYI This formula does not work if the cells are on separate pages. I don’t know why but I copied this exactly and even accounted for page references being exact. For some reason it simply does not work so this usually isn’t helpful.

Leave a Reply

Your email address will not be published. Required fields are marked *