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

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.

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.

### Named ranges

List = A2:A11

List_start = A2.

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

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

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

### Related posts:

Extract distinct unique sorted year and month list from a date series in excel

Create and sort distinct list by adjacent cell value size

Create a repeating list of numbers from 1 to Nth value

Count unique values and unique distinct values in two ranges combined

Count unique values and unique distinct values in three ranges combined in excel