Count the number of distinct subsets of a string that match a criteria
I need to create a formula to count the number of distinct subsets of a string that match a criteria.
For example.
I have a sheet with a column foo. the foo column contains the following
A-Z
A-Y
A-Z
A-X
A-Y
B-A
B-B
C-D
C-D
I need a formula that looks at this column and returns the number of distinct A(s) which would be three in my example. One for A-Z, one for A-Y and one for A-X.
Thanks in advance,
Nick
Best Answer
-
Hi @Nick DeMarco ,
Try this:
=COUNT(DISTINCT(COLLECT(FOO:FOO, FOO:FOO, FIND("A", @cell) = 1)))
It should work for your example. But, it is hard coded for your example and the logic may not work for your real use case.
Help at all?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Hi @Nick DeMarco ,
Try this:
=COUNT(DISTINCT(COLLECT(FOO:FOO, FOO:FOO, FIND("A", @cell) = 1)))
It should work for your example. But, it is hard coded for your example and the logic may not work for your real use case.
Help at all?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Mark,
Thank you! This was exactly what I needed!
Regards,
Nick
-
Happy to help. Thanks for using the Communuty.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!