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

Answer:

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

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

If unique values are values occuring twice or more but are to be summed only once, here is the formula. (Duplicates are merged into one value creating a distinct list)

Formula: =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

I have used named ranges in this article. List = A2:A11 and List_start = A2.

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

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

Related posts:

1. Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
2. Extract distinct unique sorted year and month list from a date series in excel
3. Create and sort distinct list by adjacent cell value size
4. Highlight unique values and unique distinct values in a range using conditional formatting in excel
5. Create a repeating list of numbers from 1 to Nth value
6. Filter unique text values from a range containing both numerical and text values in excel
7. Count unique distinct values using date criteria in a range in excel
8. Count unique values and unique distinct values in three ranges combined in excel
9. Count unique values and unique distinct values in two ranges combined
10. How to create a random list of unique numbers in excel