I need to count how many times a name is duplicated?

[aw73]

I have a spreadsheet with various names, Column A contains the Last Name, Column B contains the First Name. I need to determine how many times a name re-occurs. For example, Smith, John appears in the sheet 4 times. Smith, Jane appears in the list 3 times. I then need to determine the total number of people that appear more than once. In this example it would be 2.

Thank you

[Andrew-R]

Are you really using Excel 2003?

[aw73]

Unfortunately yes

[Andrew-R]

Oh dear ... well it makes things a little harder, but with your data in A1:B10 this formula should count the number of duplicate people:

=SUM(IF(($A$1:$A$10<>"")*($B$1:$B$10<>""),(COUNTIFS($A$1:$A$10,$A$1:$A$10,$B$1:$B$10,$B$1:$B$10)>1)/COUNTIFS($A$1:$A$10,$A$1:$A$10,$B$1:$B$10,B1:B10),0))

This is an array formula and must be entered with Ctrl-Shift-Enter, not just Enter.

Edited to add: Bum! Just remembered there's no COUNTIFS in 2003. Please hold.

[Marcol]

@ Andrew
COUNTIFS() will not work for 2003, it was introduced in 2007.

[Andrew-R]

Yes, I carefully reworked my formula to take out the IFERROR I'd used, posted it and spotted the COUNTIFS

I'm struggling to think of a way to do this in 2003 without a helper column.

[Fotis1991]

@ Andrew

As always have the question why some excel experts(like you) don,t like helper columns(if you like see and my thread here), can you pls, explain this?

http://www.excelforum.com/the-water-...r-columns.html

[Teethless mama]

Try this:

=SUM(N(FREQUENCY(MATCH(A1:A10&B1:B10,A1:A10&B1:B10,0),MATCH(A1:A10&B1:B10,A1:A10&B1:B10,0))>1))

Ctrl+Shift+Enter, not just Enter

[aw73]

Thank you Richard R for your efforts

Teethless mama - I tried that formula and it is returning a value of 1 even though there was no data in the sheet. When I added data replicating a name once in returned the value as 2?!

Thank you

[Teethless mama]

mabe this...

=SUM(N(FREQUENCY(IF(A1:A10&B1:B10<>"",MATCH(A1:A10&B1:B10,A1:A10&B1:B10,0)),MATCH(A1:A10&B1:B10,A1:A10&B1:B10,0))>1))

Array Formula: Press Ctrl+Shift+Enter, not just Enter

See the attached

[aw73]

That's perfect! Thank you very much!