Excel - Remove duplicates and SUM at the same time

问题:

I have a column with ID's, but they are duplicated; for instance:

"0,0,1,1,1,2,3,3,4,4, ... "

For each row, I have a given value in the other columns, for instance:

"0-24; 0-36; 1-13; 1-34; 1-23;..."

I want to keep only one row with each ID but I need to sum the values of each ID, that is, sum all the values in all columns for a given ID (0,1,2,...), which may include several rows.

Is there a easy way to do this using Excel?

Here is some sample data (table to the left) together with the desired output (tables to the right).

ID  Value           ID  Value
0   24              0   60
0   36              1   70
1   13              2   16
1   34              3   24
1   23              4   48
2   16                      
3   9                       
3   15                      
4   24                      
4   24      

回答1:

What you can do is to copy your IDs and paste them for example in another Sheet. Let's assume your original table is in Sheet1, and you copy all your IDs to column A in Sheet2.

Then you remove duplicate IDs in Sheet2:

Select column A > Data Ribbon > Data Tools > Remove Duplicates

In column B, you then put the formula:

=SUMIF(Sheet1!$A:$A, Sheet2!$A2, Sheet1!$B:$B)

Note: above formula goes into cell B2 on Sheet2, and you copy it down with pasteSpecial > only formulas.

Edit: if you still want the same number of rows etc because of the information in your other columns, just skip the "Remove duplicates"-part.