Formulas and Functions

Trying using the HAS() function in your formula.<\/p>

=COUNTIF({Client Business Sim LIVE Call Characteristic}, HAS(@cell, Description@row))<\/p>

Hope this helps,<\/p>

Dave<\/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":110940,"type":"question","name":"Count & Sum Distinct Values based on Criteria | COUNT(DISTINCT(COLLECT(...) | Returning 1 ???","excerpt":"In a ROLLUP sheet, referencing a source sheet, I'm trying to count (and sum) DISTINCT values that meet multiple criteria. My formula keeps returning \"1\", when in the shown scenario below has \"80\" distinct values. Hoping I'm just missing something simple. Rollup sheet purpose = count the distinct values rented within…","snippet":"In a ROLLUP sheet, referencing a source sheet, I'm trying to count (and sum) DISTINCT values that meet multiple criteria. My formula keeps returning \"1\", when in the shown…","categoryID":322,"dateInserted":"2023-09-28T16:59:21+00:00","dateUpdated":"2023-09-28T16:59:55+00:00","dateLastComment":"2023-09-28T17:24:39+00:00","insertUserID":118570,"insertUser":{"userID":118570,"name":"Mike Meyer","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Meyer","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!u9deN1AXpbw!Uw9fH9QLfno!Q2aja8frULU","dateLastActive":"2023-09-28T19:11:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":118570,"lastUserID":118570,"lastUser":{"userID":118570,"name":"Mike Meyer","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Meyer","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!u9deN1AXpbw!Uw9fH9QLfno!Q2aja8frULU","dateLastActive":"2023-09-28T19:11:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":14,"score":null,"hot":3391843440,"url":"https:\/\/community.smartsheet.com\/discussion\/110940\/count-sum-distinct-values-based-on-criteria-count-distinct-collect-returning-1","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110940\/count-sum-distinct-values-based-on-criteria-count-distinct-collect-returning-1","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":110940,"commentID":397489,"name":"Re: Count & Sum Distinct Values based on Criteria | COUNT(DISTINCT(COLLECT(...) | Returning 1 ???","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397489#Comment_397489","dateInserted":"2023-09-28T17:24:39+00:00","insertUserID":118570,"insertUser":{"userID":118570,"name":"Mike Meyer","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Meyer","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!u9deN1AXpbw!Uw9fH9QLfno!Q2aja8frULU","dateLastActive":"2023-09-28T19:11:02+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\/321J5EINVRFX\/count-distinct-values-40-ss-comunity-help3-28canva-29.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Count Distinct Values @ SS Comunity Help3 (Canva).png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-09-28T17:23:33+00:00","dateAnswered":"2023-09-28T17:08:10+00:00","acceptedAnswers":[{"commentID":397487,"body":"

The problem is the mix of data types in the serial number column. You will need to add a helper column (can be hidden after setting up) that converts every row within that column into text and then reference this in your formula.<\/p>

=[Serial #]@row + \"//m.santa-greenland.com/community/categories/formulas-and-functions/\"<\/p>

=[Column Name]@row plus quote quote<\/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":110932,"type":"question","name":"I am trying to convert a formula to a column formula and keep getting the same error. Why?","excerpt":"The formula I am trying to use is calculating the number of days between 2 dates: =NETDAYS([Billing Period Start]1, [Billing Period End]1) The error message I am getting is \"The column formula syntax isn’t quite right, see our help article https:\/\/help.smartsheet.com\/articles\/2481944 for more information.\" The column type…","snippet":"The formula I am trying to use is calculating the number of days between 2 dates: =NETDAYS([Billing Period Start]1, [Billing Period End]1) The error message I am getting is \"The…","categoryID":322,"dateInserted":"2023-09-28T15:51:34+00:00","dateUpdated":null,"dateLastComment":"2023-09-28T17:29:10+00:00","insertUserID":151182,"insertUser":{"userID":151182,"name":"ajuelsgaard","url":"https:\/\/community.smartsheet.com\/profile\/ajuelsgaard","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jD9qI_ZEy_o!ZBFrGdYxZ3o!H7BDI1juIh8","dateLastActive":"2023-09-28T17:28:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151182,"lastUser":{"userID":151182,"name":"ajuelsgaard","url":"https:\/\/community.smartsheet.com\/profile\/ajuelsgaard","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jD9qI_ZEy_o!ZBFrGdYxZ3o!H7BDI1juIh8","dateLastActive":"2023-09-28T17:28:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":15,"score":null,"hot":3391839644,"url":"https:\/\/community.smartsheet.com\/discussion\/110932\/i-am-trying-to-convert-a-formula-to-a-column-formula-and-keep-getting-the-same-error-why","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110932\/i-am-trying-to-convert-a-formula-to-a-column-formula-and-keep-getting-the-same-error-why","format":"Rich","lastPost":{"discussionID":110932,"commentID":397491,"name":"Re: I am trying to convert a formula to a column formula and keep getting the same error. Why?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397491#Comment_397491","dateInserted":"2023-09-28T17:29:10+00:00","insertUserID":151182,"insertUser":{"userID":151182,"name":"ajuelsgaard","url":"https:\/\/community.smartsheet.com\/profile\/ajuelsgaard","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jD9qI_ZEy_o!ZBFrGdYxZ3o!H7BDI1juIh8","dateLastActive":"2023-09-28T17:28:44+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\/O5NB4NF1D746\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-09-28T17:28:51+00:00","dateAnswered":"2023-09-28T17:01:19+00:00","acceptedAnswers":[{"commentID":397484,"body":"

You cannot use hardcoded row numbers in a column formula. change each 1 to @row. <\/p>

[Column Name]@row<\/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":[]}">