Need a Formula for Conditional text to display in a Task planner

Dear All Valuable members need a urgent help ,

I am working on a task planner which approx has 3000 rows with different sub tasks , 5 to 6 tasks together makes a main task so i need to track the progress and make a dashboard for main task , in order to do that i have to manual feed in sub task status (which keeps changing eg. work which was not started now completed or overdue ) so i need to put a formula to put in main task column which will look all 5-6 task in Sub task column and tell me the final status as per the condition applied , attaching the sample file please help , and thanks in advance

Hello,

I have attached a solution, but it involves 5 "Helper Columns".

It could be done in one formula, but it would be so complex, you would never be able to unravel it.

I have chosen to perform a number of tests, counting the number of rows in each tack, and looking for "Overdue", Completed" etc in separate columns.

Your decision to use Merged Cells also causes significant problems. This necessitated the formula in column E as it is the only way to look at where the task number changes. Secondly, you have merged cells in column C. These will have to go - you cannot copy down!

Column J gives you the answer you have asked for, but as I said, you will not be able to merge the cells.

Finally, it would be possible to create a UDF (using VBA), but some users cannot do this, or prefer not to.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

David is absolutely right about the issues caused by merged cells. They look nice, but they turn calculations into a mess. His helper column solution should work just fine. Alternatively, if you're willing to unmerge the cells, you can use some nested IF clauses. The formula below in C2, filled down, should work as long as everything is unmerged:

=IF(COUNTIFS($A$2:$A$23,"Complete",$B$2:$B$23,$B2)=COUNTIF($B$2:$B$23,$B2),"Complete",IF(COUNTIFS($A$2:$A$23,"Overdue",$B$2:$B$23,$B2)>0,"Overdue",IF(COUNTIFS($A$2:$A$23,"Not Started",$B$2:$B$23,$B2)=COUNTIF($B$2:$B$23,$B2),"Not Started","In Progress")))

You're welcome, and thank you!