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"))
Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
sum if based on values
BrigitteM
✭✭✭
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.6K Get Help
- 472 Global Discussions
- 200 Use Cases
- 513 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 81 Community Job Board
- 520 Show & Tell
- 35 Member Spotlight
- 3 SmartStories
- 307 Events
- 35 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
Check out the Formula Handbook template!