What formula should I use to convert drop down text responses to a corresponding number?

We are working on an assessment survey that has text dropdown values. Each of the text responses will be scored with a different numerical value. I have created a corresponding column to capture the score for each of the questions but am having trouble with the formula. For example, the respondent will answer question 1 with one of the 5 options:

Excellent, Very Good, Good, Fair, Poor.

Excellent will be worth 1 point, Very Good 2 points, Good, 3 points, Fair, 4 points, and Poor 5 points.

Can I write one formula to capture the corresponding number in my score column?

Thank you in advance for your help!

Sara

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would string a series of COUNTIFS together to add them up.


    =COUNTIFS([1st Question]@row:[Last Question]@row, @cell = "Excellent") + (COUNTIFS([1st Question]@row:[Last Question]@row, @cell ="Very Good") * 2) + (COUNTIFS([1st Question]@row:[Last Question]@row, @cell ="Good") * 3) + (COUNTIFS([1st Question]@row:[Last Question]@row, @cell ="Fair") * 4) + (COUNTIFS([1st Question]@row:[Last Question]@row, @cell ="Poor") * 5)

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would string a series of COUNTIFS together to add them up.


    =COUNTIFS([1st Question]@row:[Last Question]@row, @cell = "Excellent") + (COUNTIFS([1st Question]@row:[Last Question]@row, @cell ="Very Good") * 2) + (COUNTIFS([1st Question]@row:[Last Question]@row, @cell ="Good") * 3) + (COUNTIFS([1st Question]@row:[Last Question]@row, @cell ="Fair") * 4) + (COUNTIFS([1st Question]@row:[Last Question]@row, @cell ="Poor") * 5)

  • @Paul Newcome - I was looking for this formula and it worked perfectly! Thank you very much

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!