# Calculate Overall Status from Multiple Columns

✭✭✭✭
edited 08/08/22

Hello,

I have a sheet with statuses in multiple columns, and I'm calculating overall status by using CONTAINS looking at the columns. This works just fine, except for one complication. When one or more columns is Complete, and one or more columns is Not Started, I need to show the overall status as In Progress.

Any ideas on how to do this?

Thanks!

• ✭✭✭✭

Hi Andree,

Thanks. This sheet actually uses no hierarchy. I have several processes in separate columns and each can have one of 4 statuses.

I was able to get the result I need by adding 2 more columns before the Overall Status column (called NotStarted & Complete), with the following formulas respectively: =if(contains("Not Started",COL1:COL5),1 and =if(contains("Complete",COL1:COL5),1. Then in the Overall Status column, the first part of my formula is =if(NotStarted@row+Complete@row=1,"In Progress", followed by the rest of my CONTAINS statements.

It's not very elegant, but it works.

Tim

• ✭✭✭✭✭✭

I hope you're well and safe!

Try something like this.

=IF(COUNT(CHILDREN()) = 0, "", IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Not Started") > 0, "In Progress")))))

Did that work/help?

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭

Hi Andree,

Thanks. This sheet actually uses no hierarchy. I have several processes in separate columns and each can have one of 4 statuses.

I was able to get the result I need by adding 2 more columns before the Overall Status column (called NotStarted & Complete), with the following formulas respectively: =if(contains("Not Started",COL1:COL5),1 and =if(contains("Complete",COL1:COL5),1. Then in the Overall Status column, the first part of my formula is =if(NotStarted@row+Complete@row=1,"In Progress", followed by the rest of my CONTAINS statements.

It's not very elegant, but it works.