#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Need help on Summing Values based on drop down menu that contains text options

Options
edited 12/09/19

Hi, I have a question regarding using formulas and drop down menus.

Similar to an FMEA, I want to have dropdown menus (High, Med, Low) for the severity, occurrance and detectibility. Then I want to have a formula that produeces a RPN (Risk Priority Number) by assigning the value 10 to High, 7 to Med and 3 to Low and finally multiplying the numbers together.

Does anyone have any ideas how I could do this?

Thanks!

Monikka

• ✭✭✭✭✭
Options

I am assuming your columns are severity, occurence, detectibility

Are you OK with putting the value in the selecction?

10 - High

7 - Med

3 - Low

If so, you could put in your "total" column this:

=VALUE(LEFT(severity3, 1)) * VALUE(LEFT(occurrence3, 1)) * VALUE(LEFT(detectibility3, 1))

If you are not OK with that you could set up three columns and assign them values based on a nested IF statement - e.g., IF(severity3="high", 10, IF(severity3="med", 7, 3)). Then you would need to multiple the value of those columns.

• ✭✭✭✭✭✭
Options

If your three columns are consecutive, this will also work (without adding the value to the drop down)

=10 ^ COUNTIF(severity21:detectibility21, "High") * 7 ^ COUNTIF(severity21:detectibility21, "Med") * 3 ^ COUNTIF(severity21:detectibility21, "Low")

High-High-High = 1000

Low-Low-Low = 27

Low-Med-High (in any order)  = 210

I hope this helps.

Craig

• ✭✭✭✭✭✭
edited 07/07/16
Options

Also, Jamison's formula has an error for High -- which has two digits.

Craig

• Options

Thanks guys! I was playing around with the LEFT formula since yes, we will have the numbers as part of the selection. I'll give it a go and let you know how it worked!

Monikka

• ✭✭✭✭✭✭
Options

If you get stuck with the 2 digit 10 vs 1 digit 3 and 7, check out either the FIND() function (find the space) or pad the text in the drop down (03 instead of 3).

Craig

• edited 02/17/17
Options

Thanks everyone for your great suggestions. I was in fact able to implement the formula I was looking for:

Project Score=VALUE([Synthesis Score]1) * (VALUE([Success Score]1) + VALUE([Alignment Score]1) + VALUE([Manufacturing Score]1) + VALUE([Resource Score]1))

and to find the values from each of my drop down lists I used:

Alignment Score=LEFT([Alignment]1, FIND(" ", [Alignment]1))

Monikka

This discussion has been closed.