Smartsheet formula for counting different numbers in different columns and "scoring" it
So I have a bit of a convoluted one …
We are autoscoring our risks on a monthly basis to report in to Directors and Sponsors. I have managed to write the formula to count the number of red/amber/risks. I am now hoping to take it one step further and score it "1", "2", or "3" depending whether there's more red/amber/red risk. Example below:
Project #  No. of red risks  No. of amber risks  No. of green risks  Risk score 

Project1  2  1  7  
Project2  0  2  0  
Project3  5  1  1  
So the formula would have to look at each column and, for Project 1, score "3" as there's mainly green, for Project 2, score "2" as there is mainly amber and for Project 3, score it "1" as there's mainly red.
Is there an easy way to do this?
Many thanks
Mel
Answers

Hello @Mel_Barnes  Not exactly what you asked for but could you consider weighting red risks with a value of 5, amber risks with a value of 3 and green risks with a value of 1. You could then multiple the number of risks by their weighting to give an overall risk rating with the formula below to show which projects have an overall higher risk score.
=([No. of Red Risks]@row * 5) + ([No. of Amber Risks]@row * 3) + ([No. of Green Risks]@row * 1)
Help Article Resources
Categories
Check out the Formula Handbook template!