# Add multiple amounts together for multiple selections

Options
edited 08/23/23

I am trying to create a formula that can look at a column with multiple choices, put a number value for each option chosen and add them together for a total. So if they choose option A, amount is 400, option B, another 400, option C, 500, etc. the column would add those amounts together to get 1300.

Here is what I have so far:

However, the result in the column looks like this "400400500"

So it is showing the number results I want for options in that column, but how do I get it to add them together vs. just project the numbers next to each other? I tried a SUM at the beginning of the formula, but it doesn't seem to recognize the numbers. Help!

Tags:

• ✭✭✭✭✭✭
Options

The speech marks around the values are indeed the culprit, but you can simply remove these from your formula to have it work as you'd like:

This should do the trick - if you have any issues then let us know!

• ✭✭✭✭✭✭
Options

Given the 123 output I think what is happening is that the final IF statement output (i.e. the 2) is getting the multiplication by 60 and then the (2x1.5) is getting added on.

I would put an extra set of brackets round all the IF statements:

This should mean the IFs are all added together before the multiplication is done and give you the expected result (300).

Let me know if this works! 😊

• ✭✭✭✭✭✭
Options

In your formula, the numbers were being treated as text. The + sign is also the concatenate(join) sign. Your formula is building a text string with the "characters" 400400500. Throwing those numbers within the VALUE() function will force them to be treated as numbers instead of text. I also added an extra IF statement to ensure there is no weird behavior from rows with no selections.

• Options

Thank you for your response and help! However, I copied your function in, but it says that it is "UNPARSEABLE". What could be the issue?

• ✭✭✭✭✭✭
Options

The speech marks around the values are indeed the culprit, but you can simply remove these from your formula to have it work as you'd like:

This should do the trick - if you have any issues then let us know!

• Options

Thank you Nick! Removing the "" off of the numbers seems to have fixed it so it adds the numbers together correctly to get 1300. One more question, is it possible for a similar formula to take that total and multiply it by another number in another cell?

Currently, I have the formula below, but it is totaling to "123" instead of "300". It should be adding (1.5+1.5+2)*60 (the amount in the # of Guests Attending column)

• ✭✭✭✭✭✭
Options

Given the 123 output I think what is happening is that the final IF statement output (i.e. the 2) is getting the multiplication by 60 and then the (2x1.5) is getting added on.

I would put an extra set of brackets round all the IF statements:

This should mean the IFs are all added together before the multiplication is done and give you the expected result (300).

Let me know if this works! 😊

• Options

Thank you Nick! That was the solution. 🙏

• ✭✭✭✭✭✭
Options

No problem, happy to have helped! 😊

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!