Author: Oscar Cronquist Article last updated on March 13, 2018

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



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

Functions used in this blog post:

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

Adds all the numbers in a range of cells

Returns the smallest number in a set of values. Ignores logical values and text

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".