Smartsheet formula for counting different numbers in different columns and "scoring" it

Mel_Barnes

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

Protonsponge

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)