Calculate Overall Status from Multiple Columns

Tim Dollmeyer

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!

Tim Dollmeyer

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.

I appreciate your help!

Tim

Andrée Starå

Hi @Tim Dollmeyer

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!

Tim Dollmeyer

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.

I appreciate your help!

Tim