Count of Duplicates (Column B) per Name (Column A)

[DaleVacaro]

Hi All,

Sorry not good with excel formula, can you help me construct formula how can I count how many duplicates we have in Column B per Name (Column A)?

Sample result should be:

Rose - 1 (2 Red)

Joy - 1 (2 Purple)

Angel - 2 (2 Blue & 2 Red)

 

[Saba Sabaratnam]

hi

Create a helper column as shown below and enter the following formula in C1 and copy it down.


=1/COUNTIFS(A:A,A2,B:B,B2)

Then create list of names as shown above and enter the following formula in F2 (and copy it down).

=SUMIFS(C:C,C:C,"<1",A:A,E2)

Kind regards

Saba

 

[DaleVacaro]

Hey Saba! truly appreciate your help!!! It perfectly works!

 

[Saba Sabaratnam]

Happy to help

Kind regards

Saba