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?

unique-distinct-values-in-two-columns1

Answer:

unique-distinct-values-in-two-columns21

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

  1. Copy formula
  2. Select cell C12
  3. Paste formula in formula bar
  4. Press and hold Ctrl + Shift
  5. 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