How to assign values to drop-down menu options and add them up?

Audry89

Hello!

I need to assign a grade (number) to each of the drop-down menu options. It is with Mutiple selection, so I need to show in another column the sum of all the numeric values from all the options selected.

Here you can see the options in the column Stakeholder. Each option has a grade assigned. I need to show in the column "Sumatoria", the sum of the values of all the options selected in the drop-down menu.

Can you please help me? I don't know which formula to use.

Thank you!

Audry

Brian Wilson DC

Good evening @Audry89 It could be due to a long day, but I can't think of a formula at the moment that will allow you to simultaneously parse the data and then assign a value to each of them and then sum them. One work around to achieve your goal would be to use helper cells, such as below:

You have your dropdown menu with the multi-select options. Then create a column to look for the specific word that you want to assign a value to. For this example, I assigned CEO = 18, RDI = 10 and Patient = 6. Because of the multi-select box, you will want to use the CONTAINS function to search for a specific line of text. Below is the equation I used for the CEO totals column, you can modify it for each of the other dropdown columns you need to assign value to.

=IF(CONTAINS("CEO", Stakeholders:Stakeholders, 18, 0)

=IF(CONTAINS("RDI", Stakeholders:Stakeholders), 10, 0)

=IF(CONTAINS("Patients", Stakeholders:Stakeholders), 6, 0)

Then the Sumatoria column is a simple:

=SUM([CEO Totals}@row:[Patient Totals]@row)

I would hide the helper columns once you have everything set.

I hope this gives you at least a place to start!

Regards,
Brian