Article updated on March 13, 2018

I have written two blog posts about counting unique and unique distinct values in a range and two ranges combined.

Now it is time for three ranges combined.

Formula in D17:

=SUM(1/(COUNTIF(Table1, Table1)+COUNTIF(Table2, Table1)+COUNTIF(Table3, Table1)))+SUM(1/(COUNTIF(Table2, Table2)+COUNTIF(Table1, Table2)+COUNTIF(Table3, Table2)))+SUM(1/(COUNTIF(Table3, Table3)+COUNTIF(Table1, Table3)+COUNTIF(Table2, Table3))) + CTRL + SHIFT + ENTER

Formula in D21:

=SUM(IF((COUNTIF(Table1, Table1)+COUNTIF(Table2, Table1)+COUNTIF(Table3, Table1))=1, 1, 0))+SUM(IF((COUNTIF(Table2, Table2)+COUNTIF(Table1, Table2)+COUNTIF(Table3, Table2))=1, 1, 0))+SUM(IF((COUNTIF(Table3, Table3)+COUNTIF(Table1, Table3)+COUNTIF(Table2, Table3))=1, 1, 0)) + CTRL + SHIFT + ENTER

Named ranges
Table1 (A1:D4)
Table2 (A6:D9)
Table3 (A11:D14)
What is named ranges?

How to implement array formula to your workbook
Change named ranges.

count-unique-and-unique-distinct-values-in-three-multicolumn-ranges-combined.xls
(Excel 97-2003 Workbook *.xls)

The excel sample file generates new random values in all three ranges every time you press F9.