Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 2 down vote accepted

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

share|improve this answer
Thanks. This will get me where I need to be. I had hoped that I could create a function that sums the data in place, without the addition of a new column, but this will do. For future reference, I did have to make a slight modification to your function: =IF(COUNTIF($A$1:A1,A1)=1,B1,"") – Todd Benning Apr 20 '10 at 13:43
@Todd: You can hide the new column, but still get the sum of the hidden column. – Leftium Apr 20 '10 at 20:16

From Microsoft: Filter for unique values or remove duplicate values

  • To filter for unique values, use the Advanced command in the Sort & Filter group on the Data tab.
  • To remove duplicate values, use the Remove Duplicates command in the Data Tools group on the Data tab.
  • To highlight unique or duplicate values, use the Conditional Formatting command in the Style group on the Home tab.
share|improve this answer
Thanks, but I don't want to modify or hide the existing data. Highlighting gets me nowhere when it comes to calculating a sum either. – Todd Benning Apr 20 '10 at 13:34

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.