Scoring Formulas?

LVU22

I need help coming up with a formula that assigns a numeric value to a particular response in a cell of drop down options.  Multiple options can be selected.  For example, a cell drop down has four values to select from: 1A, 1B, 1C, 1D.  Each selection is assigned a value of three points.  So, if 1B and 1C are selected, the total value of the cell is 6 (3 for each).  If only 1D is selected, then the cell has a value of 3.  If 1A, 1C, 1D are selected, then the cell has a value of 9 and so on.

Any tips or advice is appreciated!

Protonsponge

Hello @LVU22,

One option that might work for you is something like the formula below:-

=IF(ISBLANK(Options@row), "", IF(HAS(Options@row, "1A"), 3, 0) + IF(HAS(Options@row, "1B"), 3, 0) + IF(HAS(Options@row, "1C"), 3, 0) + IF(HAS(Options@row, "1D"), 3, 0))

If you wanted to have different scores for options 1A, 1B, 1C, 1D you would just change the 3 in the general formula as highlighted below:

IF(HAS(Options@row, "1A"), 3, 0)

I made a quick demo below and all seems to be working

I hope that is helpful to you in some way,

Protonsponge

Protonsponge

If you will always assign 3 points to each option, a much simpler alternative would be to use the COUNTM function to count the number of options selected in your [Options] column and multiple by 3 as shown below.

=COUNTM(Options@row) * 3

Protonsponge