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)
