Summing corresponding values from a multiple drop down with more than 1 entry possible

Options

I have a reference sheet of food items (column 1) with corresponding prices (column 2):

potatoe - 3

milk - 5

onion - 1

Cheese - 2


On another sheet, I want to sum the total applicable from a multiple selection drop down cell.

for example:

If potatoe, milk, and onion were all selected within one row, my formula would return 9 right next to it in a price column.


this seems very simple now that I type it out....but, I cannot figure it out. Any help is greatly appreciated :)

Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Options

    The contents of that multi-select cell is a block of text (string). You can use IF() with FIND(), CONTAINS(), or HAS() to check for each food item. When found, substitute the numeric value for it; otherwise set the numeric value to "0".

    = IF(CONTAINS("potatoe",Items@row), 3,0) 
    + IF(CONTAINS("milk",Items@row), 5,0)
    + IF(CONTAINS("onion",Items@row), 1,0)
    + IF(CONTAINS("cheese",Items@row), 2,0)
    

    or

    =IF(CONTAINS("potatoe", Items@row), 3, 0) + IF(CONTAINS("milk", Items@row), 5, 0) + IF(CONTAINS("onion", Items@row), 1, 0) + IF(CONTAINS("cheese", Items@row), 2, 0)
    

    Hope this helped!

    Here's documentation for all Smartsheet functions.

    https://help.smartsheet.com/functions

  • AaronJones
    Options

    Thank you for the reply! This is how I currently have this solved, however, I'm at a couple paragraphs length now and the Smartsheet server gives this ALOT of issues when I need to update it. I was hoping to index a separate sheet with 2 columns for easy updates that hopefully don't continue to break the thing.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!