Scoring Formulas

Options

I'm looking to convert a process to smartsheet, but am trying to determine if there is a formula to make the concept work. Currently I have a form which asks a series of questions - each question has a single select option of "yes (description)" "no (description)" and "n/a". For each question, there is a score associated with the answer.

I successfully have this formula working: =IF([Receivership Pre]1 = "Yes, the subrecipient is in receivership", "1", "0") So essentially if someone selected "yes" to this column question, I have a separate column which will report a score of 1. If someone selected no or n/a, it will report a score of 0. That part seems to work. Ultimately I'll have 20 or so questions/column entries on a row, with 20 or so matching column entries coding the responses as 1 or 0. For that row, I then need to count each total of 1 or 0 for a total score (5, 17, who knows).

From there I'll be pulling parts of the data out into a report.

Currently though I haven't been able to find a way to count the various columns reporting a 1 or 0 function to give me a total numeric function.

Tags:

Best Answer

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭
    Answer ✓
    Options

    Actually, @Amelia C. Thibault, I figured out why it's not working in the "SUM" formula Parker recommended above. Your formula has quotes around the numerical values, and Smartsheet is reading those as text values.

    Here's your formula:

    =IF([Receivership Pre]1 = "Yes, the subrecipient is in receivership", "1", "0")

    And here's what it ought to be:

    =IF([Receivership Pre]1 = "Yes, the subrecipient is in receivership", 1, 0)

    Try that and let us know if the SUM() function works after that!

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

Answers

  • Parker Oxford
    Parker Oxford ✭✭✭✭✭
    Options

    Hi @Amelia C. Thibault


    The SUM function should be able to achieve what your intending. You can use something to the effect of the formula below to sum up multiple cells in a row. This should be able to apply as a column formula as well. Just change the column names to whatever you called the columns where the formula lives.

    =SUM([Question Answer formula1]@row,[Question Answer formula2]@row...)
    
  • Amelia C. Thibault
    Options

    @Parker Oxford Thank you - unfortunately that doesn't work. When I apply =SUM([Receivership Pre Risk]@row, [Disbarment Pre Risk]@row) it provides me an answer of 0, even though in =IF([Receivership Pre]1 = "Yes, the subrecipient is in receivership", "1", "0" my column reads "1" and my Disbarment pre risk reads 0. I should be getting a 1, not a 0. Even if I change all of my values to 1 and add every one to the SUM formula, the answer still calculates at 0.

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭
    Options

    Hey Amelia.

    Seems like Parker's solution ought to work - could you upload a screenshot?

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭
    Answer ✓
    Options

    Actually, @Amelia C. Thibault, I figured out why it's not working in the "SUM" formula Parker recommended above. Your formula has quotes around the numerical values, and Smartsheet is reading those as text values.

    Here's your formula:

    =IF([Receivership Pre]1 = "Yes, the subrecipient is in receivership", "1", "0")

    And here's what it ought to be:

    =IF([Receivership Pre]1 = "Yes, the subrecipient is in receivership", 1, 0)

    Try that and let us know if the SUM() function works after that!

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭
    Options

    PS you could use a CONTAINS function to make this easier for Yes/No responses. Something like:

    =IF(CONTAINS("Yes", [Receivership Pre]@row), 1, 0)

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!