Finding matches with multi-select dropdowns


Similar to the Josh Reilly question, I am trying to match values between a range and a dropdown list. I have a range of poisoned fruit, and a person who eats many different fruits. I want smartsheets to look through the fruits eaten dropdown list and if it finds a poisoned fruit to a) know the status(poisoned/not poisoned)b) give how many poisoned fruits were eaten and c) which ones.

a) I have had a little success so far with the IF(HAS( function as long as only the poisoned fruits are the only ones "present" in the cell. However, once i add non-poisoned fruit it says he is "not poisoned" ideas?

b) I have been trying to use the Countif(has( for finding the number of times the fruit appears but i think i am having trouble with the criteria part, it keeps producing 0.

c) Once i understand how to cross reference the range and find them in the dropdown i should be able to find a way to create a "poisoned foods eaten" column, but still any help would be appreciated!

Please note I want to avoid text specific calls at all costs! the real "poisoned fruits" list is hundreds of rows long and I don't want to manually call each one in each dropdown list to see if it was eaten.

Thanks so much!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!