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

AaronJones

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 :)

Toufong Vang

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

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.