Summing Multi-Dropdown Values

jbaranski

I have a Smartsheet with a multi-dropdown where people pick multiple tests to be run. Each of those tests has a cost that I have on a different sheet. With one selection in the test column it's easy enough to do a VLOOKUP to grab the value. Is it possible to parse through multiple entries in a cell, grab their individual costs from a table using VLOOKUP (or something else), and sum them? Or do I just need to create separate VLOOKUP columns for each test?

Screen Shot 2023-02-17 at 2.22.20 PM.png

Screen Shot 2023-02-17 at 2.22.09 PM.png

PaigeKucz

@jbaranski - i think its easiest if you add helper columns

For example: Helper: LM- hu and the column formula would be: (you will have to do this for each assay)

=SUMIF([Assays]@row, HAS(@cell, "LM- hu"), {underlying sheet with data})

then you will need a total cost column

=SUM([LM- Hu]@row + [LM - Mu]@row + [LM - Rat]@row + [LM - Cyno]@row + [LM - Dog]@row

jbaranski

Yeah that's my backup option but there are 50+ tests so trying to avoid that if I can.