Excel Sum that excludes duplicates


I have data in the following format:

Id    Value
1     20
2     40
3     20
3     20
4     50

I want to sum the value column while excluding duplicates from the id column. In this case, I should end up with a total of 130. Can anyone help?

Best Answer

You can create a new column with duplicates removed; SUM() the new column.

Use the COUNTIF() function to reduce values for duplicate ID's to 0 in the new column like this:


(example above assumes ID's are stored in column A, and their values are stored in column B)

source and more detailed info