Add multiple amounts together for multiple selections

Arianna Sikorski

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!

Nick Korna

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!

Nick Korna

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! 😊

Carson Penticuff

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)), "")

Arianna Sikorski

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?

Nick Korna

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!

Arianna Sikorski

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

Nick Korna

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! 😊

Arianna Sikorski

Thank you Nick! That was the solution. 🙏

Nick Korna

No problem, happy to have helped! 😊