MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Two lists with some names the same; can it count names that appear more than once as only one?


Posted by Merle Mason on January 23, 2001 11:53 AM

I have two lists of names; some of the names appear in both lists. Is there a way to do a COUNTA and have it only count names once? That is, if I have two lists of ten names, and five of the names are the same, is there a way to get it to return a count of 15? Thanks.


Posted by Ian on January 23, 2001 12:56 PM

There must be a better way, but here is one that should work. Assume your first list in A1..A10 (sorted) and second list is B1..B10. Do =Counta at the bottom of each for a total of 20. Then subtract duplicates by using vlookup in columns c with the following
=IF(ISNA(VLOOKUP(B1,$A$1:$A$10,1,FALSE)),0,1)
Total this and your unique records will be A+B-C. Good luck.


Posted by Mark W. on January 23, 2001 1:52 PM

Suppose A1:A10 contains {"Sally";"Mary";"Larry";"Jerry";"Linda";"Mike";"Jim";"Jake";"Moe";"John"}
and B1:B10 contains {"Mark";"Sally";"Barry";"Larry";"Peter";"Tom";"Paul";"Mary";"Moe";"Jim"}.
Then {=COUNTA(A1:A10,B1:B10)-SUM((A1:A10=TRANSPOSE(B1:B10))+0)} does what you
want. This is an array formula which must be entered using Shift+Ctrl+Enter.

Posted by Merle Mason on January 23, 2001 3:17 PM

That worked...Thanks!

Posted by Merle Mason on January 23, 2001 5:12 PM

Hi Mark,

That is unbelievable! Works like a charm. Thanks so much!