Question: I have a list of values, and i want to sum only unique values, how?

sum-unique-values

Answer:

If unique values are values occuring only once in a a list, here is the formula to sum unique values.

Array formula in cell I1:

=SUM(IF(COUNTIF(List, List)=1, List, "")) + Ctrl + Shift + Enter

Number 3 is the only unique value.

sum-unique-values3

If unique values are values occuring twice or more but are to be summed only once, here is the formula. (Duplicates numbers are removed.)

Array formula in cell E1:

=SUM(IF(IF(MATCH(List, List, 0)=(ROW(List)-ROW(List_start)+1), (ROW(List)-ROW(List_start)+1), 0)<>0, List, "")) + Ctrl + Shift + Enter

Number 2, 3 , 5, 6, 7 are unique distinct values. 2 + 3 + 5 + 6 + 7 = 23.

sum-unique-values2

Named ranges

List = A2:A11

List_start = A2.

Here is a picture from the excel file attached below the picture.

sum-unique-values4

Download excel example file.

sum-distinct-values
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test,[value_if:true],[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers

ROW(reference)
Returns the rownumber of a reference

MATCH(lookup_value,lookup_array, [match_type])
Returns the relative position of an item in an array that matches a specified value

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

SUM(number1,[number2],)
Adds all the numbers in a range of cells