Count Unique Values across two (2) columns - similar data in each column

[tlmf2222]

Hello, I am trying to find a way to count unique values across multiple columns, but not a combination of values across both columns, but rather unique values *across* both rows. For example:

Column A Column B
Mark Mary
Mark Steve
Mary Linda
Mary Steve
Steve Steve

I would want the formula to return 4, as there are 4 unique values if you look at both columns together (Mary, Mary, Steve, Linda). (It's almost as if you would combine both columns into one columns, and then count unique values). The Difference here from other similar threads I have found is that I don't care about the relationship of values between columns that are on the same row.

Is there a way to do this using a formula?

Thanks so much!

Mark

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(1-(A2:B6=""),1/COUNTIFS(A2:B6,A2:B6)))

 

[tlmf2222]

Thanks, would this work if there are blank values involved? I keep getting a returned formula result of zero. (Also, and I don't think this matters, the formula is referencing columns on a separate tab within the same worksheet).

Also, the fields that I am trying to get the unique count on are formula results, and not plan text. Not sure if that matters either...

Thanks again,

Mark

 

[Aladin Akyurek]

(1) The formula does not count blanks as a unique item.

(2) Text or number should not matter.

(3) Referencing data on a different sheet will not thwart the formula at all.

 

[tlmf2222]

Thank you all - I got it to work. Clearly the problem I was having was between keyboard and chair.

 

[Aladin Akyurek]

Thank you all - I got it to work. Clearly the problem I was having was between keyboard and chair.

You are welcome, Mark.

Aladin