Unique values

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

Unique list

Count unique values in two lists combined

count unique values in two lists combined

Array formula in D12:

=SUM(IF(COUNTIF(List1, List1)+COUNTIF(List2, List1)=1, 1))+SUM(IF(COUNTIF(List2, List2)+COUNTIF(List1, List2)=1, 1)) + CTRL + SHIFT + ENTER

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

Read more related articles