Counting Duplicate Entries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good morning, everyone!

I have a formula where I need to count the number of duplcate entries for a
particular name where it matches up with a particular country. See below:

Column A Column E
----------------------------------------

Australia AB 111
Australia XXX 011
Australia AC 211
Australia XXX 011
India XXAE 211
India AB 111

I need to make the answer say Australia has 3 unique entries for Column E.

Can anyone assist?

Thanks!
Stacy
 
Try...

=SUM(IF(FREQUENCY(IF((A1:A6<>"")*(A1:A6="Australia"),MATCH(E1:E6,E1:E6,0)
),ROW(E1:E6)-ROW(E1)+1)>0,1))

or

=COUNT(1/FREQUENCY(IF((A1:A6<>"")*(A1:A6="Australia"),MATCH(E1:E6,E1:E6,0
)),ROW(E1:E6)-ROW(E1)+1))

Both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER.

Hope this helps!
 
Back
Top