Formula needed: Multi drop down select column to output value in another column

Jenny Schee

Hi All,

Any help with a formula? This is driving me nuts!

Basically what we're hoping is to be able to select multiple values within the "Rate_Card_Tactic_T4/T5 column", and output the dollar amount that is assigned to that tactic in "T4/T5 Cost - Email Blast" column.

This is as far as I've gotten... it works only if I assign one formula, but when I try to duplicate the formula for all of the drop down list options, the dollar amount will not output into the other column.

=IF(CONTAINS("Email Blast", [Rate_Card_Tactic_T4/T5]@row), 3500)

Paul Newcome

Are you wanting to output a TOTAL? So if someone selects "Email Blast" it will output 3500, but if someone selects "Email Blast" and "Asset Pulls" it will output 3500 PLUS whatever number you want to assign to "Asset Pulls"?

Jenny Schee

Hey @Paul Newcome that's exactly it! If they select Email blast and Asset Pulls it will combine both values together. So Email blast = $3500, asset pulls = $1000, the value would be combined to $4500. There could be more than 2, at most I've seen, maybe 5 or 6 different options!

Paul Newcome

Try something like this...

Build out a table that has each selection listed out in its own row down a column. In the next column enter the corresponding amount for that single selection.

Then to get your total, you will use a formula such as this...

=SUMIFS([Table Amount Column]:[Table Amount Column], [Table Selection Column]:[Table Selection Column], HAS([Rate_Card_Tactic_T4/T5]@row, @cell))

Jenny Schee

@Paul Newcome im not sure I’m following?

Jenny Schee

Not sure a table would work because I’m trying to tie those value specific to that project line item. @Paul Newcome

Paul Newcome

If you set up a table like this...

You can use the SUMIFS above to pull the values from that table and add them together based on the selections made within the multi-select dropdown.

Jenny Schee

@Paul Newcome Yeah I dont think that will work because there could be so many different variations or project deliverables "tactics" for every project.

Paul Newcome

OK. Then in that case you will need to "add" a bunch of IF statements together which will also be variable for every project.

=IF(CONTAINS("Email Blast", Dropdown@row), 1500) + IF(CONTAINS("Asset Pulls", Dropdown@row), 3500) + IF(CONTAINS("Something Else", Dropdown@row), 1200) + IF(CONTAINS(..........................................

Jenny Schee

@Paul Newcome omg. The plus sign was what I was missing all along! YOU ARE A GENIUS!!! Thank you!!

Paul Newcome

Happy to help. 👍️

I personally still suggest trying to create a table on each of the sheets because (for me) it is much easier to leave a formula alone and update a table if anything changes whether it be a dollar amount or a new option needs added or the text itself needs tweaked instead of having to edit long and complex formulas where even one single accidental typo or deletion can break everything and could be hard to find to fix, but that is just my personal preference.