Add multiple amounts together for multiple selections
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:
=IF(CONTAINS("$400: KALE SALAD ($1.50/pp each add'l person)", [O1: Salad]@row), "400") + IF(CONTAINS("$400: ARUGULA SALAD w/ BLUEBERRIES & FETA ($1.50/pp each add'l person)", [O1: Salad]@row), "400") + IF(CONTAINS("$500: ISRAELI SALAD ($2.00/pp each add'l person)", [O1: Salad]@row), "500")
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!
Best Answers
-
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:
=IF(CONTAINS("$400: KALE SALAD ($1.50/pp each add'l person)", [O1: Salad]@row), 400) + IF(CONTAINS("$400: ARUGULA SALAD w/ BLUEBERRIES & FETA ($1.50/pp each add'l person)", [O1: Salad]@row), 400) + IF(CONTAINS("$500: ISRAELI SALAD ($2.00/pp each add'l person)", [O1: Salad]@row), 500)
This should do the trick - if you have any issues then let us know!
-
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:
=(IF(CONTAINS("$400: KALE SALAD ($1.50/pp each add'l person)", [O1: Salad]@row), 1.5) + IF(CONTAINS("$400: ARUGULA SALAD w/ BLUEBERRIES & FETA ($1.50/pp each add'l person)", [O1: Salad]@row), 1.5) + IF(CONTAINS("$500: ISRAELI SALAD ($2.00/pp each add'l person)", [O1: Salad]@row), 2)) * [# of Guests Attending (non-IKAR):]@row
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! 😊
Answers
-
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.
=IF([01: Salad]@row <> "", IF(CONTAINS("$400: KALE SALAD ($1.50/pp each add'l person)", [O1: Salad]@row), VALUE(400)) + IF(CONTAINS("$400: ARUGULA SALAD w/ BLUEBERRIES & FETA ($1.50/pp each add'l person)", [O1: Salad]@row), VALUE(400)) + IF(CONTAINS("$500: ISRAELI SALAD ($2.00/pp each add'l person)", [O1: Salad]@row), VALUE(500)), "")
-
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?
-
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:
=IF(CONTAINS("$400: KALE SALAD ($1.50/pp each add'l person)", [O1: Salad]@row), 400) + IF(CONTAINS("$400: ARUGULA SALAD w/ BLUEBERRIES & FETA ($1.50/pp each add'l person)", [O1: Salad]@row), 400) + IF(CONTAINS("$500: ISRAELI SALAD ($2.00/pp each add'l person)", [O1: Salad]@row), 500)
This should do the trick - if you have any issues then let us know!
-
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)
=IF(CONTAINS("$400: KALE SALAD ($1.50/pp each add'l person)", [O1: Salad]@row), 1.5) + IF(CONTAINS("$400: ARUGULA SALAD w/ BLUEBERRIES & FETA ($1.50/pp each add'l person)", [O1: Salad]@row), 1.5) + IF(CONTAINS("$500: ISRAELI SALAD ($2.00/pp each add'l person)", [O1: Salad]@row), 2) * [# of Guests Attending (non-IKAR):]@row
-
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:
=(IF(CONTAINS("$400: KALE SALAD ($1.50/pp each add'l person)", [O1: Salad]@row), 1.5) + IF(CONTAINS("$400: ARUGULA SALAD w/ BLUEBERRIES & FETA ($1.50/pp each add'l person)", [O1: Salad]@row), 1.5) + IF(CONTAINS("$500: ISRAELI SALAD ($2.00/pp each add'l person)", [O1: Salad]@row), 2)) * [# of Guests Attending (non-IKAR):]@row
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! 😊
-
Thank you Nick! That was the solution. 🙏
-
No problem, happy to have helped! 😊
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!