Sum unique values in excel
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:
- Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
- Extract distinct unique sorted year and month list from a date series in excel
- Create and sort distinct list by adjacent cell value size
- Highlight unique values and unique distinct values in a range using conditional formatting in excel
- Create a repeating list of numbers from 1 to Nth value
- Filter unique text values from a range containing both numerical and text values in excel
- Count unique distinct values using date criteria in a range in excel
- Count unique values and unique distinct values in three ranges combined in excel
- Count unique values and unique distinct values in two ranges combined
- How to create a random list of unique numbers in excel






Leave a Reply