Sum Formula for Adding Values

Coulter
Coulter
edited 05/10/24 in Formulas and Functions

I am trying to create a formula where it is looking at a column with a dropdown where multiple options can be selected and assigning a value to each option that may be selected. I thought that I had the formula set up correctly but keep getting the error #UNPARSEABLE.

Can someone help me understand what I might be doing wrong with the formula? When I do two IF options, it works but when I add any more than that, it seems to break.

=SUM(IF(CONTAINS("Medical, Dental, Vision", [Carrier Changing for which Benefits?]@row), 3),
(IF(CONTAINS("Spending Accounts (FSA, HSA, etc)", [Carrier Changing for which Benefits?]@row), 3),
(IF(CONTAINS("Basic Life and Disability", [Carrier Changing for which Benefits?]@row), 2),
(IF(CONTAINS("Voluntary Life and Disability", [Carrier Changing for which Benefits?]@row), 2),
(IF(CONTAINS("Critical Illness", [Carrier Changing for which Benefits?]@row), 1),
(IF(CONTAINS("Accidental Insurance", [Carrier Changing for which Benefits?]@row), 1),
(IF(CONTAINS("Other", [Carrier Changing for which Benefits?]@row), 1)))))))

Answers

  • Here is the dropdown. Not all options in the dropdown will have a value, just trying to get the formula to work for the options that will have a value. If I build in the Medical, Dental, Vision and the Spending Accounts (FSA, HSA, etc) options into the formula it will work fine. However if I build in a third option such as Basic Life and Disability, it breaks the formula. Not sure what I am doing wrong.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!