IF Statement for multiple cells with blanks

ysc12

I am trying to create an IF statement which returns a status based on the status of 6 separate cells. How do I set the status to ignore blanks from those reference cells? For the attached example, the status should be set to "Not Started" based on the columns highlighted in blue.

Andrée Starå

https://community.smartsheet.com/discussion/109687/if-statement-for-multiple-cells-with-blanks

Hi,

I hope you're well and safe!

Not sure I follow! Should the formula check all six cells or something else? What determines the Status?

Can you elaborate?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

ysc12

https://community.smartsheet.com/discussion/comment/393350#Comment_393350

Yes the formula should check all 6 columns. For example, if all six columns are either blank or passed, the output should be passed.

ysc12

I am using the below formula but receiving an error.

=IF(AND(OR(COUNTBLANK([Australia Ins Test Status]@row,[Corp Test Status]@row,[GSO Test Status]@row,[Intl MI Test Status]@row,[USMI Test Status]@row,[UK Insurance Test Status]@row)>0), OR([Australia Ins Test Status]@row = "Not Started" ,[Corp Test Status]@row = "Not Started" ,[GSO Test Status]@row = "Not Started" ,[Intl MI Test Status]@row = "Not Started" ,[USMI Test Status]@row = "Not Started",[UK Insurance Test Status]@row = "Not Started")), "Not Started", "Error")

Nick Korna

COUNTBLANK is an Excel function, not a Smartsheet one.

What are the different outcomes for the 6 status columns and what would you want to happen for the overall status?

E.g.:

If there is "Not Started" in any number of columns and all other columns are blank, "Not Started"

If there is "Completed" in a column but "Not Started" in any other column on the row, "In Progress"

If "Complete" in all columns, "Complete"

And so on.

ysc12

I figured this out - thanks all for the insight.