Adding values contingent on a multiselect dropdown
Hello!
There's something I've been struggling to implement for quite a while now, that I think must be doable, but I haven't quite sorted. I have one column that is a multiselect dropdown, in which none, one, multiple, or all of the options may be ticked off. In another column, I want a value to be returned based on that first column. Using just a multivariable IF function, it's easy to do IF one is selected, then 500, IF another, then 1000, etc. with a dropdown, but that doesn't account for the possibility of multiple selections. Basically, I'm looking for the above, but also if one and another are BOTH selected, give me 1500. I'll add a screen shot to help illustrate:
In the "Selections column, I want to be able to select any or all of Option 1, 2, and/or 3.
In the "Cost" column, I want to enter a formula (that I think should be possible), so that if Option 1 is selected in the Selections column, the Cost column returns the value "100"
If Option 2 is selected, the Cost column shows the value "150"
If Option 3 is selected, the Cost column shows the value "200"
If Option 1 and Option 2 are both selectedlike in the picturethe Cost column shows the value "250"
If all three Options are selected, the Cost column shows the value "450"
And so on.
I've tried experimenting with various permutations of IF, IFAND, IFS, SumIF, Contains, etc. but I can't find something that I think fits exactly right, so I'm hoping someone can point me in the right direction! This doesn't feel like too complicated a task, I just can't quite get there!
Thank you!
Best Answer

So I had a similar problem, here is the solution I came up with.
I had a separate resource sheet that had the values of each Resource that could be selected in the drop down. Then I referenced that sheet with this
=SUMIFS({Reference Value }, {Reference Name}, HAS([Dropdown menu]@row, @cell))
That allowed me to give each resource a different value and name.
Here is an example on one page.
Answers

So I had a similar problem, here is the solution I came up with.
I had a separate resource sheet that had the values of each Resource that could be selected in the drop down. Then I referenced that sheet with this
=SUMIFS({Reference Value }, {Reference Name}, HAS([Dropdown menu]@row, @cell))
That allowed me to give each resource a different value and name.
Here is an example on one page.

Thanks, Dan! That's really helpful and has almost solved my problem. The only remaining issue is that, set up as you suggested, I'm unable to convert it to a column formula, which I'd like to do to prevent needing to reenter it any time a new row is created. Turning the references into absolute references, I can drag the formula to all existing rows, but that won't solve the issue for future created rows, and I'm not sure what about the syntax of the formula prevents conversion to a column formula.

The formula provided by @Dan W should be able to be applied as a column formula. Are you able to provide the exact formula you are using that cannot?
Also...
I too suggest setting up the table and using a SUMIFS, but if you did not want to use that, you would use a series of IF statements, but instead of nesting them you would add them together.
=IF(HAS(Selections@row, "Option 1"), 500, 0) + IF(HAS(Selections@row, "Option 2"), 1000, 0)

You're Welcome! Definitely Share your formula so we can help figure out the column formula.

Hi Paul!
Please see the attached images.
(Note that this screenshot is taken without the $s for absolute reference, but I tried both ways and it did not make a difference in this regard)
Thanks for taking a look, and for the alternate solution if this doesn't pan out!

You will want to remove the numbers at the end of your [Selection Values]:[Selection Values] and [Selection Items]:[Selection Items]. That way it will search the whole column not just 13
=SUMIFS([Selection Values]:[Selection Values], [Selection Items]:[Selection Items], HAS(Selections@row, @cell))
Here is mine.

Ah! That did the trick; thanks, Dan!

This just solved my problem too! Thanks for sharing!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!