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

Find more posts tagged with

Accepted answers

All comments

Mark.poole

@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

BrigitteM

@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

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

BrigitteM

@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

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

https://community.smartsheet.com/discussion/comment/437149#Comment_437149

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.

BrigitteM

@MichaelTCA thank you so much!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.