Add multiple amounts together for multiple selections

Options
Arianna Sikorski
Arianna Sikorski ✭✭✭
edited 08/23/23 in Formulas and Functions

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!

Tags:

Best Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    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:

    =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
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    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:

    =(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

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    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.

    =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
    Arianna Sikorski ✭✭✭
    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?

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    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:

    =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
    Arianna Sikorski ✭✭✭
    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)


    =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
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    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:

    =(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
    Arianna Sikorski ✭✭✭
    Options

    Thank you Nick! That was the solution. 🙏

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    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!