Formula IF/CONTAINS/COUNTIF showing UNPARSABLE error.

montserrat
montserrat ✭✭
inSmartsheet Basics

I have a sheet collecting answers from a survey. I have 5 columns showing Agree or Disagree questions, one for Team/Role. I want to know the percentage of the level of agreement for each of these 5 questions for each Team/Role. Meaning I want to know the percentage of agreement for Operations, DSIT PMO, etc.

I'm trying to figure out what's wrong with my formula. I want to perform a calculation if the Text in my cell matches any of the cells in the column used as range/search_within (for instance, if my cell says DSIT PMO then give me the percentage of agreement of all the cells that match the criteria)

This is my formula: =IF(CONTAINS("DSIT PMO", {Smartsheet Feedback Survey - Team/Role}], ((COUNTIF({Smartsheet Feedback Survey - Admin Work Reduced}, "Agree") / (COUNTIF({Smartsheet Feedback Survey - Admin Work Reduced}, "Agree") + COUNTIF({Smartsheet Feedback Survey - Admin Work Reduced}, "Disagree")))))

In this formula, I'm trying to calculate the percentage for the last column (Reduced Admin work question) for DSIT PMO role/team.

Here is a screenshot from the sheet I'm getting the data from:

Screenshot (72).png

Here is one from the sheet where I'm making the calculations:

Screenshot (74).png

Any help is much appreciated. TIA

Tags:
· Share on Facebook Share on Twitter

Best Answer

  • montserrat
    montserrat ✭✭
    Answer ✓

    Just for the sake of resolving this (in case anyone else has a similar issue). I tried a different formula, not sure if it's a simpler or more complicated way but it's the one that actually worked:

    = IFERROR (((COUNTIFS({Smartsheet Feedback Survey - Easy to Use}, "Agree", {Smartsheet Feedback Survey - Team/Role}, Description@row) / (COUNTIFS({Smartsheet Feedback Survey - Easy to Use}, "Agree", {Smartsheet Feedback Survey - Team/Role}, Description@row) + COUNTIFS({Smartsheet Feedback Survey - Easy to Use}, "Disagree", {Smartsheet Feedback Survey - Team/Role}, Description@row)))), "N/A")

    I used COUNTIFS instead of COUNTIF so every time that I'm getting the # of "Agree" I'm only counting the ones that have the desired team/role.

    I hope this is clear!

    · Share on Facebook Share on Twitter

Answers

  • ker9
    ker9 ✭✭✭✭✭✭

    Hi,

    One problem might be the bracket:

    =IF(CONTAINS("DSIT PMO", {Smartsheet Feedback Survey - Team/Role}],

    Hope this helps.

    · Share on Facebook Share on Twitter
  • montserrat
    montserrat ✭✭

    Good catch@ker9! I corrected the formula but still got the same error:

    =IF(CONTAINS("DSIT PMO", [{Smartsheet Feedback Survey - Team/Role}], ((COUNTIF({Smartsheet Feedback Survey - Admin Work Reduced}, "Agree") / (COUNTIF({Smartsheet Feedback Survey - Admin Work Reduced}, "Agree") + COUNTIF({Smartsheet Feedback Survey - Admin Work Reduced}, "Disagree"))))))

    · Share on Facebook Share on Twitter
  • montserrat
    montserrat ✭✭
    Answer ✓

    Just for the sake of resolving this (in case anyone else has a similar issue). I tried a different formula, not sure if it's a simpler or more complicated way but it's the one that actually worked:

    = IFERROR (((COUNTIFS({Smartsheet Feedback Survey - Easy to Use}, "Agree", {Smartsheet Feedback Survey - Team/Role}, Description@row) / (COUNTIFS({Smartsheet Feedback Survey - Easy to Use}, "Agree", {Smartsheet Feedback Survey - Team/Role}, Description@row) + COUNTIFS({Smartsheet Feedback Survey - Easy to Use}, "Disagree", {Smartsheet Feedback Survey - Team/Role}, Description@row)))), "N/A")

    I used COUNTIFS instead of COUNTIF so every time that I'm getting the # of "Agree" I'm only counting the ones that have the desired team/role.

    I hope this is clear!

    · Share on Facebook Share on Twitter

Categories

@Kimberley O<\/a> <\/p>

An COUNTIFS Statement is what you are looking for.<\/p>

=COUNTIFS([Test passed?]:[Test passed?], =\"//www.santa-greenland.com/community/discussion/105135/\", [Check if customer cancelled or was no-show]:[Check if customer cancelled or was no-show], =1)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":109280,"type":"question","name":"What happened to the Solution Panel icon?","excerpt":"A week or so ago, I saw the new Solution Panel icon and found it so helpful. Last week, it was gone and I found a thread somewhere that said it should be back online 8\/21. However, it's still not there and I can't find the thread to check on updates. When will that feature be available again?","snippet":"A week or so ago, I saw the new Solution Panel icon and found it so helpful. Last week, it was gone and I found a thread somewhere that said it should be back online 8\/21.…","categoryID":321,"dateInserted":"2023-08-22T16:10:43+00:00","dateUpdated":null,"dateLastComment":"2023-08-22T16:48:34+00:00","insertUserID":165485,"insertUser":{"userID":165485,"name":"Nikki R.","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Nikki%20R.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!nzjWX6mhAKQ!iVdXotG08bw!7wqUQE6aMh9","dateLastActive":"2023-08-22T16:42:09+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-22T19:33:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3385445357,"url":"https:\/\/community.smartsheet.com\/discussion\/109280\/what-happened-to-the-solution-panel-icon","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109280\/what-happened-to-the-solution-panel-icon","format":"Rich","lastPost":{"discussionID":109280,"commentID":391904,"name":"Re: What happened to the Solution Panel icon?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391904#Comment_391904","dateInserted":"2023-08-22T16:48:34+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-22T19:33:12+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":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-22T16:42:20+00:00","dateAnswered":"2023-08-22T16:34:12+00:00","acceptedAnswers":[{"commentID":391895,"body":"

\n \n https:\/\/community.smartsheet.com\/discussion\/109280\/what-happened-to-the-solution-panel-icon\n <\/a>\n<\/div>\n

Hi, <\/p>

I hope you're well and safe!<\/p>

They slowed down the rollout, but it's coming.<\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic week!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":109273,"type":"question","name":"If there is no info in the second date column leave the formula cell empty or say \"incomplete\"","excerpt":"I can either get the calculation to work or I can get it to say Incomplete when the second date is empty. How do I get it to do both?","snippet":"I can either get the calculation to work or I can get it to say Incomplete when the second date is empty. How do I get it to do both?","categoryID":321,"dateInserted":"2023-08-22T15:32:56+00:00","dateUpdated":null,"dateLastComment":"2023-08-22T15:50:17+00:00","insertUserID":165479,"insertUser":{"userID":165479,"name":"Chris_Lloyd","url":"https:\/\/community.smartsheet.com\/profile\/Chris_Lloyd","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BJPvQS46LfI!5FMteRIPY_o!hcfpPpI27mo","dateLastActive":"2023-08-23T00:05:27+00:00","banned":0,"punished":0,"private":true,"label":"✭"},"updateUserID":null,"lastUserID":165479,"lastUser":{"userID":165479,"name":"Chris_Lloyd","url":"https:\/\/community.smartsheet.com\/profile\/Chris_Lloyd","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BJPvQS46LfI!5FMteRIPY_o!hcfpPpI27mo","dateLastActive":"2023-08-23T00:05:27+00:00","banned":0,"punished":0,"private":true,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":22,"score":null,"hot":3385438993,"url":"https:\/\/community.smartsheet.com\/discussion\/109273\/if-there-is-no-info-in-the-second-date-column-leave-the-formula-cell-empty-or-say-incomplete","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109273\/if-there-is-no-info-in-the-second-date-column-leave-the-formula-cell-empty-or-say-incomplete","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109273,"commentID":391859,"name":"Re: If there is no info in the second date column leave the formula cell empty or say \"incomplete\"","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391859#Comment_391859","dateInserted":"2023-08-22T15:50:17+00:00","insertUserID":165479,"insertUser":{"userID":165479,"name":"Chris_Lloyd","url":"https:\/\/community.smartsheet.com\/profile\/Chris_Lloyd","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BJPvQS46LfI!5FMteRIPY_o!hcfpPpI27mo","dateLastActive":"2023-08-23T00:05:27+00:00","banned":0,"punished":0,"private":true,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/0RVDWZJTQ4LD\/smartsheet.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Smartsheet.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-22T15:50:00+00:00","dateAnswered":"2023-08-22T15:48:37+00:00","acceptedAnswers":[{"commentID":391857,"body":"

I think this will do what you are looking for:<\/p>

=IF(ISDATE([ASSMT DATE]@row), IF(ISDATE([AUTH'D DATE]@row), [AUTH'D DATE]@row - [ASSMT DATE]@row, \"Incomplete\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=321&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":4972,"limit":3},"title":"Trending in Smartsheet Basics","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Smartsheet Basics

  • Counting symbols in two columns

    I am trying to determine a formula that will count the number of "flags" in one column if there is not a symbol in the second column. In the example below, there is only one flag that I want counted. Basically, the green/red/yellow symbol would cancel out counting the red flag if that makes sense.

  • What happened to the Solution Panel icon?

    A week or so ago, I saw the new Solution Panel icon and found it so helpful. Last week, it was gone and I found a thread somewhere that said it should be back online 8/21. However, it's still not there and I can't find the thread to check on updates. When will that feature be available again?

  • If there is no info in the second date column leave the formula cell empty or say "incomplete"

    I can either get the calculation to work or I can get it to say Incomplete when the second date is empty. How do I get it to do both?

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video