Some questions to ********:

You have several lines in Sheet1 with P1, but in Sheet2 there will only be one P1 , the value of that should be the result of that comes out of the function, correct??

And for the function:
You have the possible states
"In Progress"
"Completed"
"Yet to start"

You say if only one is "Yet to Start" the result has to be "Yet to start" (easy), but for the other two you say the result will be "in Progress" or "Completed" if all the values for the PX are of that state. What should be the result if there are some of both states??