Array Formula #UNPARSEABLE

edited 12/06/22 in Formulas and Functions

I am trying to utilize an array formula in order to calculate a net "score" based on a combination of selected responses in a Smartsheet form. Essentially: Based on pre-assigned values (1, 2, or 3) for each selectable response, I'd like the form to auto-calculate the cumulative value based on the combination of responses selected.

Using the following formula in Excel seemed to work, however it gives me #UNPARSEABLE in Smartsheet.

Each response is assigned a value on a reference sheet (Figure A)

In this example, all possible responses are selected (Figure B)

Therefore, I would expect a cumulative calculated score of "21" (Figure C)

However, I am currently getting #UNPARSEABLE using the following formula:

=SUM(IF(ISNUMBER(SEARCH({Impact Options Range 1},[Impact]@row,@cell)),{Impact Options Range 2}))

  • Range 1 = Response options
  • Range 2 = Numerical value assigned to the response (1, 2, or 3)


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!