sum if based on values

Options

Hi Smartsheet Community,

I would like to sum a range of cells, assigning a numerical value to the values (dropdown values) assigning a numerical value to the values in the cell. if the value is 'often' 3 points, the value 'sometimes' is 2 points, and the value 'rarely' is 1 point, I want to get the total in one cell without having to create a column per if statement

ex. there's 8 questions, if I answer 'often' to all of them, the value in the sum cell should be 24

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/21/24
    Options

    @BrigitteM

    Create a "Helper" Column. Lets call it Score with the following formula

    =IF([Dropdown column]@row = "often",3,IF([Dropdown column]@row ="sometimes",2,IF([Dropdown column]@row ="rarely",1)))

    Then use this new column as the reference.

    =Sum(Score:Score) if in the same sheet.

    =Sum({Score Ref}) if in a Seperate sheet

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • BrigitteM
    BrigitteM ✭✭✭
    Options

    @Mark.poole thank you for your response! I am trying to see if there is any way to avoid 1 helper column per question because it will be a 20+ question form for our users to fill out, each question will receive a value that needs to be summed

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/21/24
    Options

    @BrigitteM

    Will every question have the same 3 possible answers?

    1 helper column is all you need. just turn it into a column formula. The issue would be the more possible answers with scores the longer the compounded if statement becomes.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • BrigitteM
    BrigitteM ✭✭✭
    Options

    @Mark.poole yes, the only options are 'Often' which will be 3 points, 'Sometimes' will be 2 points, and 'Rarely' will be 1 point, after the users answer all questions (on a form), I would like one formula to give me the person's score

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭
    Options

    Hello @BrigitteM

    Based on your example, you could use a SUM() and COUNTIF() function.

    =SUM((COUNTIF(range,"often")*3),(COUNTIF(range,"sometimes")*2),COUNTIF(range,"rarely"))

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    Thing is. I never would of thought of doing it this way. Nor can I see any reason it won't work. I'm going to use this for now on instead of using helper columns to achieve the same thing.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • BrigitteM
    BrigitteM ✭✭✭
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!