# Scoring Matrix Formula

Options
✭✭

I'm looking for a formula to act as an automatic scoring matrix. The first part of the score is determined by a dropdown of 3 values (met = 0, not met = -1, and n/a = blank). The next and final part of the score is determined by a symbol color (green, red, and yellow). See image for visuals.

• ✭✭✭✭✭✭
Options

You would use something like this:

=IF(Condition@row = "NOT MET", -1, 0) - IF([Column2]@row = "Red", 5, IF([Column2]@row = "Yellow", 3, 1))

• ✭✭✭✭✭✭
Options

So to make sure I understand...

If it is "N/A", then there should be no score at all, if it is "MET" then the score should be zero, otherwise follow the scoring chart?

=IF(Condition@row <> "N/A", IF(Condition@row = "MET", 0, -1 -IF([Column2]@row = "Red", 5, IF([Column2]@row = "Yellow", 3, 1))))

• ✭✭✭✭✭✭
Options

You would use something like this:

=IF(Condition@row = "NOT MET", -1, 0) - IF([Column2]@row = "Red", 5, IF([Column2]@row = "Yellow", 3, 1))

• ✭✭✭✭✭✭
Options

Hi @younge

This should do what you require

=SUM(IF(Condition@row = "Met", 0, IF(Condition@row = "N/A", "", IF(Condition@row = "Not Met", -1, "Error"))), IF([Column2]@row = "Red", -5, IF([Column2]@row = "Yellow", -3, IF([Column2]@row = "Green", -1, "Error"))))

Tested as below and working

Hope that helps

Thanks

Paul

• ✭✭
Options

@Paul Newcome This works great thanks! I need a small change to the formula, however. I realized that I articulated the question slightly incorrect. If the condition = met then i would like the score to be 0 regardless of the color, otherwise the rest of the scoring will remain as described. Also this formula returns N/A as a score instead of leaving it blank (the picture was probably misleading).

• ✭✭✭✭✭✭
Options

So to make sure I understand...

If it is "N/A", then there should be no score at all, if it is "MET" then the score should be zero, otherwise follow the scoring chart?

=IF(Condition@row <> "N/A", IF(Condition@row = "MET", 0, -1 -IF([Column2]@row = "Red", 5, IF([Column2]@row = "Yellow", 3, 1))))

• ✭✭
Options

That's it! You're a Smartsheet angel @Paul Newcome

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!