text to formula help needed: risk colors

Hi everyone,

I have a risk tracker basic risk colors (red, green, amber, grey) for 4 risk categories (scope, budget, resources, schedule) and I want to turn them into a number to help me generate an overall risk level for each project.
I would like to give the following values: green=1, amber=10, red=100, grey=0, so a project with 1 amber and 3 green statuses would have a Risk Score of 103 (examples below).

Could someone please help me with a formula to calculate the Risk Score automatically based on the Red/Amber/Green/Grey values in the risk columns?



Thanks in advance !

Try this

Bob Phillips said:

Try this

Code:

=SUMPRODUCT(COUNTIF(B2:E2,{"Green","Amber","Red","Grey"})*{1,10,100,1})

Click to expand...

Thanks a lot Bob !

Bob Phillips said:

Try this

Code:

=SUMPRODUCT(COUNTIF(B2:E2,{"Green","Amber","Red","Grey"})*{1,10,100,1})

Click to expand...

apologies for the extra spam. I know I am doing something wrong but I can't figure out what.
Hopefully the attachment with the error screenshot works.

What SPAM???

What is your PC's locale? I suggest you try this:

=SUMPRODUCT(COUNTIF(B2:E2;{"Green";"Amber";"Red";"Grey"})*{1;10;100;1})

AliGW said:

What SPAM???

What is your PC's locale? I suggest you try this:

=SUMPRODUCT(COUNTIF(B2:E2;{"Green";"Amber";"Red";"Grey"})*{1;10;100;1})

Click to expand...

Thanks a lot. That did it :brick:
Swiss PC setup.

= European locale = semi-colons instead of commas (because of the potential confusion with a decimal separator)

Glad to have helped.