Article updated on September 20, 2013

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.


Download excel example file.

(Excel 97-2003 Workbook *.xls)

Functions in this article:

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Counts the number of cells in a range that contain numbers

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

Counts the number of cells within a range that meet the given condition

Adds all the numbers in a range of cells