Count unique distinct values in two columns in excel
Question: I have values in two not adjacent columns. I want to know how many unique distinct values there are in those two columns combined?
Answer:
Formula in C12:
=SUM(1/COUNTIF(List1, List1))+SUM(IF(COUNTIF(List1, List2)=0, 1/COUNTIF(List2, List2), 0))
How to create an array formula
- Copy formula
- Select cell C12
- Paste formula in formula bar
- Press and hold Ctrl + Shift
- Press Enter
Named ranges
List1 (A3:A8)
List2 (C4:C9)
What is named ranges?
How to customize the formula to your excel workbook
Change the named ranges.
Download excel example file
count-unique-distinct-values-in-two-columns.xls
(Excel 97-2003 Workbook *.xls)
Functions used in this blog post:
COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition
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
SUM(number1,[number2],)
Adds all the numbers in a range of cells
Related posts:
Count matching cell values in two columns in excel
Count unique distinct values in two columns with date criteria in excel
Comparing two columns and sum unique values using array formula in excel
Count unique distinct values in three columns combined in excel
Recommended categories
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.