Sum number based on corresponding unique value
The formula in cell E14 adds a number from column C if the corresponding value in column B is unique and returns the total for all unique values. Unique values are values that exist only once in the list, in other words, there is only one instance of the value.
Formula in cell E14:
Explaining formula in cell E14
Step 1 - Count values in column B
The COUNTIF function counts values based on a condition, I am using multiple conditions, in this case.
COUNTIF($B$3:$B$12,$B$3:$B$12)
becomes
COUNTIF({"AA";"BB";"CC";"BB";"AA";"DD";"FF";"EE";"DD";"VV"}, {"AA";"BB";"CC";"BB";"AA";"DD";"FF";"EE";"DD";"VV"})
and returns
{2;2;1;2;2;2;1;1;2;1}
Step 2 - Check if unique
The equal sign compares each value in the array to 1 and returns either TRUE or FALSE.
COUNTIF($B$3:$B$12,$B$3:$B$12)=1
becomes
{2;2;1;2;2;2;1;1;2;1}=1
and returns
{FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE}
Step 3 - Multiply with numbers in column C
(COUNTIF($B$3:$B$12,$B$3:$B$12)=1)*$C$3:$C$12
becomes
{FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE}*$C$3:$C$12
becomes
{FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE}*{2;5;3;6;1;8;7;9;3;5}
and returns
{0;0;3;0;0;0;7;9;0;5}
Step 4 - SUM numbers
The SUMPRODUCT function then adds the numbers and returns the total to cell E14.
SUMPRODUCT((COUNTIF($B$3:$B$12,$B$3:$B$12)=1)*$C$3:$C$12)
becomes
SUMPRODUCT({0;0;3;0;0;0;7;9;0;5})
and returns
24 in cell E14.
Download Excel *.xlsx file
5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Extract unique values from two columns
I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]
Filter unique values and sort based on adjacent date
The formula in cell F3 extracts unique values from column C, the formula in cell F3 extracts the corresponding dates […]
List all unique events in a month
Question: I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to […]
Filter unique values from a cell range
Unique values are values occurring only once in cell range. This is what I am going to demonstrate in this blog […]
Find min and max unique and duplicate numerical values
Question: How do I get the largest and smallest unique and duplicate value? The image below shows you a list […]
Create numbers based on numerical ranges
The image above shows an array formula in cell B3 that calculates numbers based on the numerical ranges in cell […]
Filter unique words from a range [UDF]
This blog post describes how to create a list of unique words from a cell range. Unique words are all […]
Filter unique values sorted from A to Z
A unique value is a value that only exists once in a list. A unique distinct list contains all cell values […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.