Count unique distinct values in two columns
Formula in C12:
How to create an array formula
- Double click on cell C12
- Paste above formula
- Press and hold Ctrl + Shift
- Press Enter
Explaining formula in cell C12
Step 1 - Count values in cell range B3:B8
The COUNTIF function counts values equal to a condition or criteria.
COUNTIF($B$3:$B$8, $B$3:$B$8)
becomes
COUNTIF({"AA";"BB";"AA";"CC";"BB";"AA"}, {"AA";"BB";"AA";"CC";"BB";"AA"})
and returns
{3;2;3;1;2;3}
Step 2 - Divide 1 with array
1/COUNTIF($B$3:$B$8, $B$3:$B$8)
becomes
1/{3;2;3;1;2;3}
and returns
{0.333333333333333;0.5;0.333333333333333;1;0.5;0.333333333333333}
Step 3 - Sum values
The SUM function simply adds the numbers and returns the total.
SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))
becomes
SUM({0.333333333333333;0.5;0.333333333333333;1;0.5;0.333333333333333})
and returns 3.
Step 4 - Which values exist in cell range $D$3:$D$8
COUNTIF($B$3:$B$8, $D$3:$D$8)=0
becomes
COUNTIF({"AA";"BB";"AA";"CC";"BB";"AA"}, {"BB";"CC";"DD";"CC";"BB";"BB"})=0
becomes
{2;1;0;1;2;2}=0
and returns
{FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}
Step 5 - Convert TRUE to corresponding number
The IF function uses a logical expression (argument1) to determine which value to return (TRUE - argument2 , FALSE - argument3)
IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)
becomes
IF({FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)
becomes
IF({FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}, {0.333333333333333;0.5;1;0.5;0.333333333333333;0.333333333333333}, 0)
and returns
{0;0;1;0;0;0}.
Step 6 - Sum array
SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0))
becomes
SUM({0;0;1;0;0;0})
and returns 1.
Step 7 - Add numbers
SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0))
becomes
3+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0))
becomes
3+1
and returns 4 in cell D12.
Download Excel *.xlsx file
Count unique distinct values that meet multiple criteria
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
Count unique distinct values within same week, month or year
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]
Count unique distinct values based on a condition
The following article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image […]
Count unique distinct values that meet multiple criteria
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
Count Conditionally Formatted cells
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
Count cells between specified values
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
15 Responses to “Count unique distinct values in two columns”
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.
Hi, hope you can help me.
If I have the below data:
A B
1 1233 MEL
2 4562 MEL
3 1233 MEL
4 7625 SYD
5 7352 SYD
6 4562 MEL
7 2447 SYD
How do I find out how many unique codes there are in column A from “MEL” in B? I’ve tried multiple formulas but they keep coming up as zero, whereas in this case the answer should be 2.
Thanks!
Bet,
read this post:
Count unique distinct values that meet multiple criteria in excel
Hi,
How would you count three columns of unique users? I have three worksheets of people who have received training, some people are on multiple worksheets and multiple times. I just want to find how many unique users there are in total.
Angelica,
Try this array formula:
=SUM(IF(List1<>"",1/COUNTIF(List1,List1),0))+SUM(IF((COUNTIF(List1,List2)=0)*(List2<>""),1/COUNTIF(List2,List2),0))
It counts unique values from two columns with blanks. List1 and List2 are named ranges.
Three ranges are more complicated.
Thanks Oscar, I tried the formula but it didn't work. I think because of the "" part. I got a #Value! error.
I'm not that familiar with "", so I tried taking out , but that didn't work, then I tried taking out "". That didn't work either.
I already named my lists Basics, Advanced and Contribute since I have three columns in three separate tabs. Basics is a column from B3 to B301, Advanced is from B3 to 101, Contribute is from B3 to B101.
The formula I tried was:
=SUM(IF(Basics"",1/COUNTIF(Basics,Basics),0))+SUM(IF((COUNTIF(Basics,Contribute)=0)*(Contribute""),1/COUNTIF(Contribute,Contribute),0))
Any advice would be appreciated!
Those right and left carets aren't showing up in my post. But I did use them.
Angelica,
Those right and left carets aren't showing up in my post. But I did use them.
That is how you recognize an array formula in excel because it is enclosed in curly brackets { }.
There are instructions in this post about how to create array formulas.
Also I should mention that the list grows weekly and the Lists I have created have several blank cells which is why I couldn't get your example to work for my problem.
Hello,
I have data in the following format:
Longitude Latitude Magnitude
72.87 33.73 7.6
69.45 37.15 6.9
69 34.5 5.3
69.1 34.5 7.2
71.8 34.8 5.3
75.5 33.5 7.6
75 34 6.9
73.23 33.37 6.9
77 35 6.1
76 34 5.3
72.3 33.9 6.1
80 30 7.5
75 34 6.9
80 31.3 7
75 34 7
76 34 5.3
75 34 6.7
75 34 6.5
80 30 6.1
79 31.5 7.5
79 30 6.9
I want to count number of magnitude values in column three in 0.1*0.1 latitude and longitude e.g (Between 76.1 long and 34.1 Lat).
Can somebody please help me? I shall be thankful
Muhammad Waseem
Muhammad Waseem,
I am not sure I understand, you want to count longitude values less than 76.1 and latitude values more than 34.1?
Dear Oscar,
Thank you very much for your response. Your comment is helpful. Actually, I am interested in counting in numbers between 34.1 and 34.2, 76.1 and 76.2: 34.2-34.3, 76.2-76.3 and so on.
Thank you
Muhammad Waseem
Muhammad Waseem,
Download *.xlsx file
count-long-and-latv2.xlsx
Dear Mr. Oscar,
Thank you very much for the help and for the file.
Regards,
Muhammad Waseem
Hi Oscar, hope you can help me.
I have two columns, 'A. Call Subject' and 'B. Issue'.
I am trying to report the number of Issues that are related to Call Subject.
The problem is that text in 'B. Issue' can be "other" for many 'A. Call Subject'.
An example, 'A. Call Subject' cells have the following text - "Sales", "Technical", "Other" - and 'B. Issues' has texts of - "New Product" and "Other" -.
How can I count the number of "other" from 'B. Issues' that are related to 'A. Call Subject' of Sales" or Technical.
Hi Mr. Oscar - thank you for a great and very clear article! I have used your suggestion successfully.
Question: Is there a way to achieve the same result (a count of unique values) using the Excel UNIQUE function instead of this approach? My 2 columns of data includes both text strings and real numbers, so would need to handle those.
Thank you again! (Also love your profile!!)