Count Unique Records in 2 Columns

[cameronb]

I have searched the forums and found many posts on this topic, but not one which resolves the below issue.

If you had 2 columns of data: A with christian names B with surnames

E.g.

A B
David Smith
Peter Smith
Chris Brown
Peter Smith
And you wanted to count the unique number of names in columns A and B, is there a formulae which can compute this, excluding blanks. In the above example it would be 3.

I have tried a variety of sumproduct and frequency formulae to no evail include the likes of:

=SUMPRODUCT(--(FREQUENCY(MATCH(A1:A4&B1:B4,A1:A4&B1:B4,0),ROW(A1:B4))>0))

Any help on this I would be most grateful.

 

[barry houdini]

Try this formula courtesy of Ron Coderre

=SUMPRODUCT((A1:A4&B1:B4<>"")*(MATCH(A1:A4&B1:B4,A1:A4&B1:B4,0)=ROW(A1:B4)-ROW(A1)+1)+0)

 

[DonkeyOte]

Array wise:

=SUM(IF(FREQUENCY(IF(A1:A4&B1:B4<>"",MATCH(A1:A4&"@"&B1:B4,A1:A4&"@"&B1:B4,0)),ROW(A1:A4)-ROW(A1)+1)>0,1))
confirmed with CTRL + SHIFT + ENTER

SUMPRODUCT wise

edit: below eff. same as BH's above

=SUMPRODUCT((A1:A4&B1:B4<>"")*(MATCH(A1:A4&"@"&B1:B4,A1:A4&"@"&B1:B4,0)=(ROW(A1:A4)-ROW(A1)+1)))

below would work but would be open to inaccuracy re:blanks whereas the first two would not.

=SUMPRODUCT((FREQUENCY(MATCH(A1:A4&"@"&B1:B4,A1:A4&"@"B1:B4,0),ROW(A1:A4)-ROW(A1)+1)>0)+0)

 

[MisterBate5]

Try

=SUM(IF(A1:A10&B1&B10=A1:A10&B1:B10,1,0))

entered as array formula (CTRL+SHIFT+ENTER)

FORGET IT - I just found a problem in my formula and when correcting it, found the formula did not work. apologies.

 

[cameronb]

Thanks very much all, I think Barry has nailed it!

 

[Sabriana]

Just wanted to say a BIG thanks for the solution above. It solved my problem too! Just wish I could understand the durn code!
But Thanks.