Unique text values in non adjacent columns

[rjwalker2514]

Column A Column C

Green Red
Red Orange
Orange Blue
Blue Green
Green Purple
Red Black

How do i set up a formula to show the number of unique entries across those two columns? The correct answer is 6 (Green, Red, Orange, Blue, Purple, Black)

 

[etaf]

what version of excel
=SUMPRODUCT((A1:B6<>"")/COUNTIF(A1:B6,A1:B6&""))

 

[rjwalker2514]

Office 2016

The problem I'm having is that the data i want to count unique values of is columns A&C but there is other data in column B which I want the formula to ignore

 

[etaf]

=SUM(1/COUNTIF($A$3:$A$8, $A$3:$A$8))+SUM(IF(COUNTIF($A$3:$A$8, $A$3:$A$8)=0, 1/COUNTIF($C$3:$C$8, $C$3:$C$8), 0))

Count unique distinct values

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are merged into one distinc

www.get-digital-help.com

 

[rjwalker2514]

This is what I had originally. I have applied it to the actual data which I know the answer is 164 but the formula gives the result of 0.3333333

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E
1	Column A		Column C		Shared Uniques
2	Green		Red		6
3	Red		Orange
4	Orange	Pink	Blue
5	Blue	mauve	Green
6	Green		Purple
7	Red		Black
8
Sheet5

Cell Formulas
Range	Formula
E2	E2	=SUM(SIGN(FREQUENCY(IFERROR(SMALL(IFERROR(IFERROR(MATCH(A2:C1000,A2:A1000,0),MATCH(A2:C1000,C2:C1000,0)+1000),""),ROW(1:1000)),""),ROW(1:2000))))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Eric W]

Shorter, and more in line with the previous formula:

=SUM(IFERROR((A2:C10<>"")*(COLUMN(A2:C10)<>2)/(COUNTIF(A2:A10,A2:C10)+COUNTIF(C2:C10,A2:C10)),0))

confirmed with Control+Shift+Enter.