Formula Help
Not the greatest at creating formulas so I am looking for some assistance on this one.
=IF(CONTAINS("0", [Product Categories selected]@row:[Keyword field filled]@row), "Complete", "Incomplete")
Here is what I am looking to do: If the four columns are all "O" then mark the status of Incomplete but if all four columns are all "1" then mark it as Complete. Also is there any way for the formula to be nested meaning every time import a new sheet the formula stars so that I can use this for other imported data?
Best Answer

Woops, sorry. I tried to write the formula without testing in Smartsheet first and missed a few parenthesis.
=IF(SUM([Product Categories selected]@row:[Keyword field filled]@row) = 0, "Incomplete", IF(SUM([Product Categories selected]@row:[Keyword field filled]@row) = 4, "Complete", "In Progress"))
Jason Tarpinian  Sevan Technology
Smartsheet Aligned Partner
Answers

Since you are strictly using numbers, it might be easier to use SUM instead of CONTAINS:
=IF(SUM[Product Categories selected]@row:[Keyword field filled]@row=0, "Incomplete",IF(SUM[Product Categories selected]@row:[Keyword field filled]@row=4,"Complete","In Progress"))
This will check if the sum of all 4 is 0, then "Incomplete". If sum of all 4 = 4, "Complete". Else "In Progress".
To make it less prone to human error (which I am a big fan of), you can use checkboxes as well for your 4 columns instead of numbers, so people don't accidentally type in something wrong. Then you could use this formula:
=IF(COUNTIFS([Product Categories selected]@row:[Keyword field filled]@row, 1) = 0, "Incomplete", IF(COUNTIFS([Product Categories selected]@row:[Keyword field filled]@row, 1) = 4, "Complete", "In Progress"))
Jason Tarpinian  Sevan Technology
Smartsheet Aligned Partner

Thank you for the help! When I added this formula below, it gave me an unparsable error...
=IF(SUM[Product Categories selected]@row:[Keyword field filled]@row=0, "Incomplete",IF(SUM[Product Categories selected]@row:[Keyword field filled]@row=4,"Complete","In Progress"))

Woops, sorry. I tried to write the formula without testing in Smartsheet first and missed a few parenthesis.
=IF(SUM([Product Categories selected]@row:[Keyword field filled]@row) = 0, "Incomplete", IF(SUM([Product Categories selected]@row:[Keyword field filled]@row) = 4, "Complete", "In Progress"))
Jason Tarpinian  Sevan Technology
Smartsheet Aligned Partner

Thank you @Jason Tarpinian, that worked! Have a great night!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!