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!
Best 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"
Answers
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 67 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!