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

06/30/16 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?






  • JamisonJamison ✭✭✭✭✭

    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.





  • J. Craig WilliamsJ. Craig Williams Top Contributor

    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.



  • J. Craig WilliamsJ. Craig Williams Top Contributor
    edited 07/07/16

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


  • 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!



  • J. Craig WilliamsJ. Craig Williams Top Contributor

    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).



  • edited 02/17/17

    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))



This discussion has been closed.