Count Distinct Values that only appear multiple times
I have a formula that follows this structure
=count(distinct(collect(range, crit_range1, crit1, etc)))
that allows me to count the number of unique entries that meet a set of criteria. Now I'm trying to modify that formula that allows me to count the number of repeat entries that meet a set of criteria.
In the original formula, I'd get a result of 4 (assuming this list met all the criteria). With the new formula that I'm asking for help with, I'd get a result of 2 (John and James show up multiple times on the list).
Best Answer
-
Hello @Jeremy Center
In my approach, I needed a helper column (checkbox) to accomplish this. You can slide the checkbox column to the far right and hide it. I would make this a column formula
In the helper column
=IF(COUNTIFS([your john doe column]:[your john doe column], [your john doe column]@row) > 1, 1)
The above is finding any entry that occurs in your name column more than once.
Then bring this helper column into your Count/Distinct/Collect where that helper column has a 1
=COUNT(DISTINCT(COLLECT([your john doe column]:[your john doe column], [your john doe column]:[your john doe column], <>"", [helper checkbox]:[helper checkbox],1)))
The DISTINCT will only count the checkbox columns once.
does this work for you?
Kelly
Answers
-
Hello @Jeremy Center
In my approach, I needed a helper column (checkbox) to accomplish this. You can slide the checkbox column to the far right and hide it. I would make this a column formula
In the helper column
=IF(COUNTIFS([your john doe column]:[your john doe column], [your john doe column]@row) > 1, 1)
The above is finding any entry that occurs in your name column more than once.
Then bring this helper column into your Count/Distinct/Collect where that helper column has a 1
=COUNT(DISTINCT(COLLECT([your john doe column]:[your john doe column], [your john doe column]:[your john doe column], <>"", [helper checkbox]:[helper checkbox],1)))
The DISTINCT will only count the checkbox columns once.
does this work for you?
Kelly
-
I love clever solutions! Thank you for your help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
