Count names that appear multiple times

hookerj

Hello. I have a sheet that has a list of names. Some of the names appear more than once. I would like to get a total of how many times a name appears more than once. I do not want to total by name, I want a general total of how many duplicates exist.

I ran data analytics and it tells me there are 117 duplicate names. I would like that in formula form so that I can put that total in a help column and display it as a metric on a dashboard.

Thank you!

Paul Newcome

You would need to include a helper column on the source sheet with a column formula along the lines of

=IF(COUNTIFS([Name Column]:[Name Column], @cell = [Name Column]@row) > 1, 1 / COUNTIFS([Name Column]:[Name Column], @cell = [Name Column]@row))

Then you can use a basic SUM function on this helper column.

Paul Newcome

You would need to include a helper column on the source sheet with a column formula along the lines of

=IF(COUNTIFS([Name Column]:[Name Column], @cell = [Name Column]@row) > 1, 1 / COUNTIFS([Name Column]:[Name Column], @cell = [Name Column]@row))

Then you can use a basic SUM function on this helper column.

hookerj

Thank you, Paul!

Paul Newcome

Happy to help. 👍️