Adding values contingent on a multiselect dropdown

Elite

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 multi-select 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 multi-variable 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 selected--like in the picture--the 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!

Dan W

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.

Dan W

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.

Elite

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.

Paul Newcome

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)

Dan W

@Elite

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

Elite

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!

Dan W

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 1-3

=SUMIFS([Selection Values]:[Selection Values], [Selection Items]:[Selection Items], HAS(Selections@row, @cell))

Here is mine.

Elite

Ah! That did the trick; thanks, Dan!

Alegna

This just solved my problem too! Thanks for sharing!