Formula combining COUNT,DISTINCT & CONTAINS
Hello SS Community,
Can you guide me on a formula to deal with the above data set?
In plain english, I need a Count of Distinct strings in the 'Release Set No." column Containing "Annual"
The answer in this case would be 8.
Is this possible on SS?
Thanks for your help!
Best Answer
-
Paul Newcome ✭✭✭✭✭✭It would look like this:
=COUNT(DISTINCT(COLLECT([Release Set No.]:[Release Set No.], [Release Set No.]:[Release Set No.], CONTAINS("Annual", @cell))))
Answers
-
Paul Newcome ✭✭✭✭✭✭
It would look like this:
=COUNT(DISTINCT(COLLECT([Release Set No.]:[Release Set No.], [Release Set No.]:[Release Set No.], CONTAINS("Annual", @cell))))
-
Jayadeep K R ✭✭
YES, thank you Paul! Works
Using the 'Collect' formula was the missing link!
Help Article Resources
Categories
=COUNT(DISTINCT(COLLECT([Release Set No.]:[Release Set No.], [Release Set No.]:[Release Set No.], CONTAINS(\"Annual\", @cell))))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108611,"type":"question","name":"Formula to show only certain parent row names","excerpt":"Hello! I have figured out how to specify if a row is a parent\/child row (we call them Project\/Tasks). I am trying to find a formula that will automatically pull from a specific parent hierarchy. In this case, it will always be the parents that have only been indented once (rows 22, 31, and 38). Rows 32 through 37 show what…","snippet":"Hello! I have figured out how to specify if a row is a parent\/child row (we call them Project\/Tasks). I am trying to find a formula that will automatically pull from a specific…","categoryID":322,"dateInserted":"2023-08-07T15:36:49+00:00","dateUpdated":null,"dateLastComment":"2023-08-07T19:15:06+00:00","insertUserID":164670,"insertUser":{"userID":164670,"name":"roxBVL","title":"Head of Project Management","url":"https:\/\/community.smartsheet.com\/profile\/roxBVL","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtet_1_O3hfNEQvdhSwdf3unZ2EL9OFZQ82YyWNvl3Hg=s96-c","dateLastActive":"2023-08-07T19:49:54+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-07T21:40:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":17,"countViews":81,"score":null,"hot":3382868515,"url":"https:\/\/community.smartsheet.com\/discussion\/108611\/formula-to-show-only-certain-parent-row-names","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108611\/formula-to-show-only-certain-parent-row-names","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108611,"commentID":389331,"name":"Re: Formula to show only certain parent row names","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389331#Comment_389331","dateInserted":"2023-08-07T19:15:06+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-07T21:40:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/9IO3X6Y36SV6\/children.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"children.jpg"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-07T19:49:50+00:00","dateAnswered":"2023-08-07T16:20:56+00:00","acceptedAnswers":[{"commentID":389275,"body":"
Ok. What if we do this (column formula):<\/p>
=IF(COUNT(ANCESTORS([Task Name]@row))>1, INDEX(ANCESTORS([Task Name]@row), 2))<\/p>
<\/p>
I've never needed the first column reference before, but maybe it is just being picky for some reason.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108606,"type":"question","name":"How do I calculate the count of project days within a quarter given the project start and end dates?","excerpt":"I have a sheet with all of the current projects, each project is along one row. I'm looking to create a column formula that is a count of project days if the project falls within a specific quarter. I've tried to type this out but cannot get the syntax right in smartsheets. I was thinking it would be one IFS statement with…","snippet":"I have a sheet with all of the current projects, each project is along one row. I'm looking to create a column formula that is a count of project days if the project falls within…","categoryID":322,"dateInserted":"2023-08-07T14:45:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-07T15:41:06+00:00","insertUserID":164666,"insertUser":{"userID":164666,"name":"mlj","url":"https:\/\/community.smartsheet.com\/profile\/mlj","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-07T20:01:24+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-07T21:40:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":26,"score":null,"hot":3382842987,"url":"https:\/\/community.smartsheet.com\/discussion\/108606\/how-do-i-calculate-the-count-of-project-days-within-a-quarter-given-the-project-start-and-end-dates","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108606\/how-do-i-calculate-the-count-of-project-days-within-a-quarter-given-the-project-start-and-end-dates","format":"Rich","lastPost":{"discussionID":108606,"commentID":389244,"name":"Re: How do I calculate the count of project days within a quarter given the project start and end dates?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389244#Comment_389244","dateInserted":"2023-08-07T15:41:06+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-07T21:40:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-07T20:01:21+00:00","dateAnswered":"2023-08-07T15:41:06+00:00","acceptedAnswers":[{"commentID":389244,"body":"
There is no IFS function in Smartsheet. Try this instead.<\/p>
<\/p>
=MAX(NETWORKDAYS(MAX([Start Date]@row, DATE(2023, 01, 01)), MIN([End Date]@row, DATE(2023, 03, 31))), 0)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
