Need help with formulas or Solution in combination of IF, OR & AND

[Vinod Krishna.C]

Dear All,

I need to summary status of a KT Plan by Projects.

To start with, I've Project A whose status will be derived from the statuses of its tasks

Ex 1: IF Status of Task 1, Task 2, Task 3 and Task 4 are "In-Progress" then Project A status should be " KT In-Progress"
Ex 2: IF Status of any one of Tasks are "In-Progress" then Project A status should be " Partial KT In-Progress"
Ex 3: IF Status of Task 1, Task 2, Task 3 and Task 4 are "Yet to Start" then Project A status should be " KT Yet to Start"

I've attached the excel template and my formula in cell B4 is as follows:

=IF(OR(B8="Yet to Start",B12="Yet to Start",B16="Yet to Start",B20="Yet to Start"),"KT - Yet to Start",IF(OR(B8="In-Progress",B12="In-Progress",B16="In-Progress",B20="In-Progress"),"KT In-Progress","Partial KT In-Progress"))

Please also be informed that project can have multiple other statuses and we should be able to leverage our formula in an efficient manner.

Any help on the same is much appreciated.

Thanks,
Vinod Krishna