Author: Oscar Cronquist Article last updated on August 12, 2018

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)