# 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:

• ✭✭✭✭✭
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.

And here's what it ought to be:

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

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!

• ✭✭✭✭✭
Options

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...)
```
• 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.

• ✭✭✭✭✭
Options

Hey Amelia.

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

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!

• ✭✭✭✭✭
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.

And here's what it ought to be:

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

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!

• ✭✭✭✭✭
Options

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

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!

• Options

@Brett Wyrick Thank you!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!