Multiple IF Statement Returning Different Text
Hi Everyone,
I am trying to write a formula that will look at a cell and if certain wording is not in the cell then return a value to a new cell. The information is from a multiple dropdown selection column. Here is an example of what i am trying to do.
Dropdown List
Would Populate the Missing Color column based on a formula so it would end up looking like this in the sheet
The formula I am trying will populate the first missing color but I can't figure out how to populate all the missing colors. Here is what I have for the formula:
=IF(OR(NOT(CONTAINS("Blue,", Color@row))), "Blue, ", IF(OR(NOT(CONTAINS("Green,", Color@row))), "Green, ", IF(OR(NOT(CONTAINS("Red,", Color@row))), "Red, ", IF(OR(NOT(CONTAINS("Yellow,", Color@row))), "Yellow, ", IF(OR(NOT(CONTAINS("Black,", Color@row))), "Black, "))))) ETC
Any help would be greatly appreciated.
Answers


=IF(FIND("Black", Color@row) = 0, "Black, ", "") + IF(FIND("Blue", Color@row) = 0, "Blue, ", "") + IF(FIND("Brown", Color@row) = 0, "Brown, ", "") + IF(FIND("Gold", Color@row) = 0, "Gold, ", "") + IF(FIND("Green", Color@row) = 0, "Green, ", "") + IF(FIND("Red", Color@row) = 0, "Red, ", "") + IF(FIND("Yellow", Color@row) = 0, "Yellow", "")
...and as a single line...
=IF(FIND("Black", Color@row) = 0, "Black, ", "") + IF(FIND("Blue", Color@row) = 0, "Blue, ", "") + IF(FIND("Brown", Color@row) = 0, "Brown, ", "") + IF(FIND("Gold", Color@row) = 0, "Gold, ", "") + IF(FIND("Green", Color@row) = 0, "Green, ", "") + IF(FIND("Red", Color@row) = 0, "Red, ", "") + IF(FIND("Yellow", Color@row) = 0, "Yellow", "")
Hi, @Grete Willstrom , this will work. Use FIND() to check if the text (color) exists. Add it to the string if FIND()=0. Do this for each "color".
If you don't what to have the hanging ", " like in the first row below, use the IF() statement below to remove it.
IF( RIGHT(xx, 2) = ", " , LEFT(xx, LEN(xx)2) , xx ) where xx is the formula above.

The above formula worked great. Now I am trying to do something where I create a value for each color then sum them together to get a total value. I am trying the same formula but it is not working. Here is what I am entering =IF(FIND("Black", Color@row) =1, 12, 0) +
IF(FIND("Blue", Color@row) = 1, 5, 0) +
IF(FIND("Brown", Color@row) = 1, 10, 0) +
IF(FIND("Gold", Color@row) = 1, 2, 0) +
IF(FIND("Green", Color@row) = 1, 8, 0) +
IF(FIND("Red", Color@row) = 1, 9, 0) +
IF(FIND("Yellow", Color@row) = 1, 15, 0)
I can get it to return the first value but after I am struggling. Please advise on what I am missing. Thanks for all the previous help.

I actually just tried the AI generator with the formula. The change that needs to be made is to update "find" to "contains" then it works.
IF(CONTAINS(("Blue", Color@row) = 1, 5, 0) +
IF(CONTAINS(("Brown", Color@row) = 1, 10, 0) +
IF(CONTAINS(("Gold", Color@row) = 1, 2, 0) +
IF(CONTAINS(("Green", Color@row) = 1, 8, 0) +
IF(CONTAINS(("Red", Color@row) = 1, 9, 0) +
IF(CONTAINS(("Yellow", Color@row) = 1, 15, 0)
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!