sum if based on values

✭✭✭

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

• ✭✭✭✭✭✭
edited 05/21/24

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.

• ✭✭✭

@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

• ✭✭✭✭✭✭
edited 05/21/24

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.

• ✭✭✭

@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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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.

• ✭✭✭

@MichaelTCA thank you so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!