Excel Sum Function to Exclude Duplicates

microsoft-excel-2007worksheet-function

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?

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)

In B1 enter:

=LARGE($A$1:$A$9,1)

In B2 enter the Array Formula:

=MAX(IF(A$1:A$9<B1,A$1:A$9))

and copy down. Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

Basically, we exclude previously found items from the Large() / Max() range.

We can use a Helper column ~~to avoid array formulas~~. In D1 enter 1, in D2 enter:

=IF(COUNTIF($A$1:A1,A2)>0,0,1)

and copy down. This "marks" the values to be added. Then in another cell:

=SUMPRODUCT(C:C*D:D)