counting names in column and matches phone numbers

hi i need help with the following
in column D I need to know how many smiths there are and if the phone number matches the same surname
if the phone number doesn't match its is differance smith and so on down column D
i have included a file

zbor's revised version should account for your prior post.

If you're using XL2007 you should (IMO) really use COUNTIFS in this instance

Please Login or Register  to view this content.

change ranges to suit requirements

=if(match($b4,b$4:$b4,0)>=row($a1),countifs(b$4:b$19,$b4,c$4:c$19,$c4),"")

Originally Posted by CWE

Your formulae works only when th range sorted in acsending or descending order.

True to an extent - the sort order is irrelevant - what is important is that the data is "grouped" together based on combinations.

And yes this was the assumption made in my formula - given this was the implied setup in the original.

Thanks for taking the time to review & adapt though of course you need to search for the combination of B & C not just B.

Worth adding perhaps that the exact MATCH test (0) to handle unsorted data would be slower so if not needed don't use it.

True also...