Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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:

=IF(COUNTIF($A$1:A1,A1)=1,B1,"")

(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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .