# How to create formulas when multiple rules are involved?

✭✭✭✭✭✭

Hi I am trying to build the following:

There are 5 criteria that will indicate the Project Health Status.

The Health Status will be base on the following rules:

1) Green: 4 indicators or more in “Green”, none in “RED”.

2) Yellow: None in “Red”, with less than 4 indicators in "Green".

3) Red: One or more indicators are in “Red”.

Anyone has any idea how can I arrive at the above rules with formula?

I can only come out with the 3rd logic: If any is "Red", then "Red".

But the other 2 logics has 2 rules to it.

Any assistance would be appreciated.

Thank you!

• ✭✭✭✭✭✭
edited 06/04/21 Answer ✓

Hi @Vivien Chong

Hope you are fine, please try the following formula and convert it to column format formula:

=IF(AND(COUNTIFS([Criteria 1]@row:[Criteria 5]@row, "Green") >= 4, COUNTIFS([Criteria 1]@row:[Criteria 5]@row, "Red") = 0), "Green", IF(AND(COUNTIFS([Criteria 1]@row:[Criteria 5]@row, "Green") < 4, COUNTIFS([Criteria 1]@row:[Criteria 5]@row, "Red") = 0), "Yellow", "Red"))

the following screenshot shows the result

bassam.khalil2009@gmail.com

☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

• ✭✭✭✭✭✭
edited 06/04/21 Answer ✓

Hi @Vivien Chong

Hope you are fine, please try the following formula and convert it to column format formula:

=IF(AND(COUNTIFS([Criteria 1]@row:[Criteria 5]@row, "Green") >= 4, COUNTIFS([Criteria 1]@row:[Criteria 5]@row, "Red") = 0), "Green", IF(AND(COUNTIFS([Criteria 1]@row:[Criteria 5]@row, "Green") < 4, COUNTIFS([Criteria 1]@row:[Criteria 5]@row, "Red") = 0), "Yellow", "Red"))

the following screenshot shows the result

bassam.khalil2009@gmail.com

☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

• ✭✭✭✭✭✭

Thanks @Bassam Khalil it works. Thanks for the prompt assistance. Much appreciated.

• ✭✭✭✭✭✭

You are welcome and I will be happy to help you any time.

bassam.khalil2009@gmail.com

☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!