SUM of unique values in a dataset

There are multiple way to sum unique values in a dataset using Excel. The situation comes up with you have a list of data that is duplicate or repeating and you don’t want to count the duplicate values, or in other words count duplicate only once, otherwise you get to a chaotic solution as Relativity painting.

Lets say we have the following dataset:

To achieve that there are few methods to the madness:

Method 1

Using UNIQUE function is the way to go! Its a relatively new hidden function that summarizes the data into unique values. All we have to do is to wrap SUM function around it.

We can apply it as this to the above dataset:

Method 2

Using SUMPRODUCT function with a Helper column. First create a helper column C where count of Seller is used. If the count is more than 1 than put 0. The formula for the helper column is:

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

After that use a SUMPRODUCT to sum each of the Seller salary by that helper.

Voila, here are some of the way to calculate sum of unique values. I am still missing a DISTINCTSUM() function in Excel that could do the same as above, but to no avail 🙂