Array Formula #UNPARSEABLE

hongliuw

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)

Julio S.

Hi @hongliuw ,

Please try with =SUMIF({Impact Options Range 1}, HAS(Impact@row, @cell), {Impact Options Range 2}) on your Impact Score Column.

I hope this can be of help

Cheers!

Julio

Julio S.

Hi @hongliuw ,

Please try with =SUMIF({Impact Options Range 1}, HAS(Impact@row, @cell), {Impact Options Range 2}) on your Impact Score Column.

I hope this can be of help

Cheers!

Julio

hongliuw

That worked - thanks so much!