Formula IF/CONTAINS/COUNTIF showing UNPARSABLE error.

montserrat

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

montserrat

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!

ker9

Hi,

One problem might be the bracket:

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

Hope this helps.

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

montserrat

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!