Article updated on March 13, 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?




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
(Excel 97-2003 Workbook *.xls)

Functions used in this blog post:

Counts the number of cells within a range that meet the given condition

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Adds all the numbers in a range of cells