## Count unique values in two lists combined in excel

*Article last updated on December 03, 2010*

**Unique values**

Unique values are values existing only once in a list or range. See picture below.

### Count unique values in two lists combined

Array formula in D12:

**Named ranges**

List1 (B3:B8)

List2 (D3:D7)

What is named ranges?

**How to customize the formula to your excel workbook**

Change the named ranges.

**Download excel example fil****e**

Count-unique-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

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 […]

Counting conditionally formatted cells (vba)

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

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]

5 easy ways to extract unique distinct values

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

Count cells between a given value

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]

Filter unique values and sort based on adjacent date

Question: How do I filter unique rows and sort by date? Answer: Column A and B are the original list. […]

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 […]

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 […]

### 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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form