Assigning Score to Multi-Select Dropdown Menu Options

MiguelAHernandez

Hi,

I need assistance with assigning a score for outputs using a multi-select dropdown menu. Essentially, I want to be able to assign a numerical value when someone selects a choice from a multi-select dropdown, however, I am only able to assign a score for one answer. When multiple options are selected, the IF formula I'm using does not work. But, when one choice is selected, the IF formula works.

Here's an example of the IF formula I'm using:

=IF([Which fruits are being addressed?]1 = "apples", 1, IF([Which fruits are being addressed?]1 = "oranges", 1, IF([Which fruits are being addressed?]1 = "bananas", 1, IF([Which fruits are being addressed?]1 = "berries", 1, IF([Which fruits are being addressed?]1 = "watermelons", 1, IF([Which fruits are being addressed?]1 = "other", 0)))))))

Additional context, the way I'm counting this information is by using a different column. So using another column to score the options in a different column, all on the same sheet.

I also want to be able to assign different scores for when someone selects multiple choices in general. For example, if someone selected one choice, they would get a "1" (unless they select Other, they would get a "0"). But if they selected additional choices, the score would increase by ".5", with the max score being "1.5".

For example, if someone selects "Apples" and "Oranges", they would get a score of 1.5. If they only selected "Apples", they would get a 1. If someone selects "Apples", "Oranges", and "Berries, they would still get a 1.5.

Thanks in advance for any helpful answers!

Larry

I would use COUNTM as I have done in the formula below

=IF([Which fruits are being addressed?]@row = "Other", 0, IF(COUNTM([Which fruits are being addressed?]@row) > 1, 1.5, IF(COUNTM([Which fruits are being addressed?]@row) = 1, 1)))