Formula needed: Multi drop down select column to output value in another column
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)
Answers
-
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"?
-
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!
-
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))
-
@Paul Newcome im not sure I’m following?
-
Not sure a table would work because I’m trying to tie those value specific to that project line item. @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.
-
@Paul Newcome Yeah I dont think that will work because there could be so many different variations or project deliverables "tactics" for every project.
-
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(..........................................
-
@Paul Newcome omg. The plus sign was what I was missing all along! YOU ARE A GENIUS!!! Thank you!!
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!