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

• Count unique values in a column in excel
• Count unique distinct values in two columns in excel
• Count unique distinct values in three columns combined in excel
• Count unique and unique distinct values in a multicolumn range in excel
  
• Count unique values and unique distinct values in two ranges combined
  
• Count unique values and unique distinct values in three ranges combined in excel
• How to count unique combined column values
• How to count unique distinct records in a date range
  
• Count unique distinct records in a date range and a numeric range in excel
  
• Count unique distinct values in two columns with date criteria in excel
  
• Count unique distinct months in excel
• Count duplicate distinct values in a column in excel

Related posts:

1. Count unique distinct values in two columns in excel
2. Count unique distinct values in two columns with date criteria in excel
3. Count unique values in two lists combined in excel
4. Count unique values and unique distinct values in two ranges combined
5. Count unique values and unique distinct values in three ranges combined in excel
6. Extract a list of duplicates from three columns combined using array formula in excel
7. Count unique distinct values in a column in excel
8. Extract a unique distinct list from three columns in excel
9. Count unique and unique distinct values in a multicolumn range in excel
10. Extract a list of duplicates from two columns combined using array formula in excel