Article updated on March 13, 2018

Question: How do I count unique distinct values in three different columns, not necessarily adjacent?

Answer:

Formula in B14:

=SUM(IF(MATCH(List1, List1, 0)>=(ROW(List1)-MIN(ROW(List1))+1), 1, 0), 0)+SUM(IF(ISERROR(MATCH(List2, List1, 0)), IF(MATCH(List2, List2, 0)>=(ROW(List2)-MIN(ROW(List2))+1), 1, 0), 0))+SUM(IF(ISERROR(MATCH(List3, List2, 0))*ISERROR(MATCH(List3, List1, 0)), IF(MATCH(List3, List3, 0)>=(ROW(List3)-MIN(ROW(List3))+1), 1, 0), 0)) + CTRL + SHIFT + ENTER

Named ranges
List1 (A5:A9)
List2 (B4:B6)
List3 (C6:C9)
What is named ranges?

How to customize the formula to your excel workbook
Change the named ranges, they don´t have to be adjacent.

Download excel example file
count-unique-distinct-values-in-three-columns.xls
(Excel 97-2003 Workbook *.xls)

Functions used in this blog post:

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

ROW(reference) returns the rownumber of a reference

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

SUM(number1,[number2],)
Adds all the numbers in a range of cells

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

ISERROR(value)
Checks whether a value is an error and returns TRUE or FALSE

This blog article is one out of twelve articles on the same subject "count unique and unique distinct".