Formula Help

Manuel Lemus

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?

Jason Tarpinian

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

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

Manuel Lemus

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

Jason Tarpinian

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

Manuel Lemus

Thank you @Jason Tarpinian, that worked! Have a great night!