Multi Drop Down Formula
Hi everyone,
I am trying to put together a formula for an ethics check process.
The user needs to select all of the items in the drop down list in order for the client to pass the ethics check.
I would like to set a formula that automates Red, Amber, Green.
I would like the following;
Green if ALL of the options are selected.
Amber if 1 the options is selected
Red if 2 or more of the options are selected
I'm not sure where I am going wrong in the below formula.
=IF(CONTAINS([Check 1]@row = "Does not provide pay day lending or equivalent services"), ([Check 1]@row = "Is not a political party"), ([Check 1]@row = "Is not based in a country/territory known to have acted in violation of human rights"), ([Check 1]@row = "Does not manufacture or sell arms"), ([Check 1]@row = "Does not manufacture, sell or provide surveillance products or services"), ([Check 1]@row = "Does not manufacture, sell or provide adult entertainment goods or services"), ([Check 1]@row = "Does not manufacture, sell or provide tobacco or tobacco replacement services"), "Green")
Please could someone advise?
Thank you in advance
Answers
-
Try this...
=IF(COUNTM([Check 1]@row) = 7, 1)
-
Brilliant! Thank you Paul - this works.
Here's the complete formula for future ref
=IF(COUNTM([Check 1 Criteria]@row) = 7, "Green", IF(COUNTM([Check 1 Criteria]@row) = 6, "Yellow", IF(COUNTM([Check 1 Criteria]@row) = 5, "Yellow", IF(COUNTM([Check 1 Criteria]@row) = 4, "Yellow", IF(COUNTM([Check 1 Criteria]@row) = 3, "Yellow", IF(COUNTM([Check 1 Criteria]@row) = 2, "Yellow", IF(COUNTM([Check 1 Criteria]@row) = 1, "Yellow", IF(COUNTM([Check 1 Criteria]@row) = 0, "Red"))))))))
-
Try this simplified version...
=IF(COUNTM([Check 1 Criteria]@row) = 7, "Green", IF(COUNTM([Check 1 Criteria]@row) = 0, "Red", "Yellow"))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!