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?
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.
Keep selecting the content in Column C and click Remove Duplicates in Data tab.
Select Continue with the current selection and hit Remove Duplicates…
Check My data has headers since Column C contain a header “Name”. Click OK to continue.
Now the duplicates have been removed in Column C.
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.
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.
You’ve got the result of the summation of all the people in the list without any duplicate.
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.