Unparseable Message on Column Formula
I can't get this formula to work in my spreadsheet, any suggestions on how to fix it?
=countif([Column5]@row11:[Column16]@row11, "*"&[Column17]$10&"*")
Best Answer
-
Paul Newcome ✭✭✭✭✭✭It looks like you are trying to count how many cells between [Column5] and [Column16] (on each row) contains the value in [Column17]$10. Is that correct?
Answers
-
Paul Newcome ✭✭✭✭✭✭
It looks like you are trying to count how many cells between [Column5] and [Column16] (on each row) contains the value in [Column17]$10. Is that correct?
-
Yes!
-
Paul Newcome ✭✭✭✭✭✭
Formula syntax between Excel and Smartsheet isn't always compatible. Give this a try:
=COUNTIFS([Column5]@row:[Column16]@row, CONTAINS([Column17]$10, @cell))
-
That worked!! Thank you!!
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
=TODAY() - IF([Assigned Date]@row <> \"\", [Assigned Date]@row, DATEONLY(Created@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":[]},{"discussionID":109212,"type":"question","name":"INDEX MATCH with multiple values question","excerpt":"Hello, I have 2 sheets. Response Form Assignment tracker Both sheets have the same following columns: Evaluator Name Candidate Name I have a check box column in Assignment Tracker that I'd like to check off if the Candidate name AND the Evaluator name match in both sheets. There will be duplicate results for each column…","snippet":"Hello, I have 2 sheets. Response Form Assignment tracker Both sheets have the same following columns: Evaluator Name Candidate Name I have a check box column in Assignment Tracker…","categoryID":322,"dateInserted":"2023-08-21T17:26:43+00:00","dateUpdated":null,"dateLastComment":"2023-08-21T20:29:09+00:00","insertUserID":165425,"insertUser":{"userID":165425,"name":"MarcM","title":"","url":"https:\/\/community.smartsheet.com\/profile\/MarcM","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-21T20:24:56+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-22T00:53:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":27,"score":null,"hot":3385290352,"url":"https:\/\/community.smartsheet.com\/discussion\/109212\/index-match-with-multiple-values-question","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109212\/index-match-with-multiple-values-question","format":"Rich","lastPost":{"discussionID":109212,"commentID":391719,"name":"Re: INDEX MATCH with multiple values question","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391719#Comment_391719","dateInserted":"2023-08-21T20:29:09+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-22T00:53:34+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-21T20:28:55+00:00","dateAnswered":"2023-08-21T19:17:27+00:00","acceptedAnswers":[{"commentID":391690,"body":"
I think I would just use a COUNTIFS in this situation:<\/p>
=IF(COUNTIFS({FY24 Hirevue Response Sheet Range 1}, [Evaluator Name]@row, {FY24 Hirevue Response Sheet Range 2}, [HV Candidate Name]@row) > 0, 1, \"\")<\/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":109221,"type":"question","name":"Auto populate with current, past, or upcoming text based on year","excerpt":"I am trying to auto populate a column with \"Current\", \"Past\", or \"Upcoming\" based on the year column. So for a row with 2023, I would want \"Current\", but would want \"Past\" or \"Upcoming\" based on what's in the Year column. I've put in the following formula but get an error (#UNPARSEABLE) =IF(Year@row = YEAR(TODAY()),…","snippet":"I am trying to auto populate a column with \"Current\", \"Past\", or \"Upcoming\" based on the year column. So for a row with 2023, I would want \"Current\", but would want \"Past\" or…","categoryID":322,"dateInserted":"2023-08-21T18:28:43+00:00","dateUpdated":null,"dateLastComment":"2023-08-21T18:51:59+00:00","insertUserID":165112,"insertUser":{"userID":165112,"name":"hdierkers","title":"Associate Director Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/hdierkers","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-21T18:51:15+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":165112,"lastUser":{"userID":165112,"name":"hdierkers","title":"Associate Director Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/hdierkers","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-21T18:51:15+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":35,"score":null,"hot":3385288842,"url":"https:\/\/community.smartsheet.com\/discussion\/109221\/auto-populate-with-current-past-or-upcoming-text-based-on-year","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109221\/auto-populate-with-current-past-or-upcoming-text-based-on-year","format":"Rich","lastPost":{"discussionID":109221,"commentID":391688,"name":"Re: Auto populate with current, past, or upcoming text based on year","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391688#Comment_391688","dateInserted":"2023-08-21T18:51:59+00:00","insertUserID":165112,"insertUser":{"userID":165112,"name":"hdierkers","title":"Associate Director Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/hdierkers","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-21T18:51: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-21T18:51:50+00:00","dateAnswered":"2023-08-21T18:50:14+00:00","acceptedAnswers":[{"commentID":391687,"body":"
See if this works for you:<\/p>
=IF(Year@row = YEAR(TODAY()), \"Current\", IF(Year@row < Year(today()),\"Past\", IF(Year@row > YEAR(today()), \"Upcoming\", 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":[]}">
