Excel formula to count duplicates as one

Question

I am trying to create a formula which will count all rows (duplicates as one) based on a value in another column.

Column A	Column B.
p1	Joe Bloggs
p2	Bob Bloggs
p1	Joe Bloggs
p3	John Bloggs
p1	Mary Bloggs
p1	Alan Bloggs

Answer 1

You can use array formula:

=SUM(--(FREQUENCY(IF(A2:A10=E2,MATCH(B2:B10,B2:B10,0)),ROW(B2:B10)-1)>0))

Array formula after editing is confirmed by pressing ctrl + shift + enter