Count names that appear multiple times
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!
Best Answer
-
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.
Answers
-
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.
-
Thank you, Paul!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!