how to do sort and count?

Originally Posted by wei82

For example i have many name and address....how to do a sort for address and then count how many of them haf duplicate address...means the same address. e.g
col1 col2
john address1
Alex address2
Mic address2
Elic address3
Wee address4
ben address5
lou address5
Bob address6

Count=2 (coz address2 & address5 is duplicate)

Anyone knows the function?

Try this:

To count duplicate address.

C1

=SUM(IF(FREQUENCY(IF(COUNTIF(B2:B9,B2:B9)>1,MATCH("~"&B2:B9,B2:B9&"",0)),ROW(B2:B9)-ROW(B2)+1),1))

Ctrl+Shift+Enter not just enter.

Hope this helps.

Hi,

Use countif() formula

That'll work.