Formula IF/CONTAINS/COUNTIF showing UNPARSABLE error.
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:
Here is one from the sheet where I'm making the calculations:
Any help is much appreciated. TIA
Best 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!
Answers

Hi,
One problem might be the bracket:
=IF(CONTAINS("DSIT PMO", {Smartsheet Feedback Survey  Team/Role}],
Hope this helps.

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"))))))

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!