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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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"))))))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this simplified version...

    =IF(COUNTM([Check 1 Criteria]@row) = 7, "Green", IF(COUNTM([Check 1 Criteria]@row) = 0, "Red", "Yellow"))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!