## 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 fil****e**

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

### Category: Count values

This post demonstrates how to build an array formula that counts unique distinct values using a criterion or criteria. Tip! […]

Comments(93) Filed in category: Count values, Excel, Unique distinct values

Question: I have a list of values (A1:A6), how do I count unique distinct values? Answer: Table of Contents Count […]

Comments(26) Filed in category: Count values, Excel, Unique distinct values, Unique values

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

Comments(26) Filed in category: Conditional formatting, Count values, Excel, User defined functions (udf), VBA

Someone googled "Count records between date range" and landed on my website. I realize I have not covered this problem. […]

Comments(22) Filed in category: Count values, Dates, Excel

Question: How do I find the number of occurances a word exists in a range of cells? It does not […]

Comments(19) Filed in category: Count values, Excel

### Category: Unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values

This post demonstrates how to build an array formula that counts unique distinct values using a criterion or criteria. Tip! […]

Comments(93) Filed in category: Count values, Excel, Unique distinct values

Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]

Comments(78) Filed in category: Excel, Unique distinct values

The formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. Unique […]

Comments(53) Filed in category: Excel, Sorted unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

Comments(45) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

### 14 Responses to “Count unique distinct values in two columns in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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.