Tracking completion of a task based on a subset of actions required

[gloop]

Hello

I'm looking for an (array?) formula which will

1) Look up all relevant columns for a task which contain separate actions
2) Look up the phase the tasks are relevant against
3) Check the values of the cells all equal "Yes"
4) If all values are "Yes", output "Yes"
5) If any one value is not "Yes", output "No"


6) Be indexed on the name of the Task, so that the tasks can move/change name and the formulae still work

Below is some sample data which should better explain what I'm looking for. The top table is where I can't figure out the formula!

I really appreciate your help. Please let me know if I can provide more information!

Phases</SPAN>	Task 1</SPAN>	Task 2</SPAN>
Phase 1</SPAN>	No</SPAN>	Yes</SPAN>
Phase 2</SPAN>	Yes</SPAN>	No</SPAN>
Phase 3</SPAN>	No</SPAN>	No</SPAN>
Task/Action per phase</SPAN>
	Task 1</SPAN>	Task 1</SPAN>	Task 1</SPAN>	Task 2</SPAN>	Task 2</SPAN>
	Action 1</SPAN>	Action 2</SPAN>	Action 3</SPAN>	Action 1</SPAN>	Action 2</SPAN>
Phase 1</SPAN>	Yes</SPAN>	Yes</SPAN>	No</SPAN>	Yes</SPAN>	Yes</SPAN>
Phase 2</SPAN>	Yes</SPAN>	Yes</SPAN>	Yes</SPAN>	No</SPAN>	No</SPAN>
Phase 3</SPAN>	No</SPAN>	No</SPAN>	Yes</SPAN>	Yes</SPAN>	No</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=6></COLGROUP>

 

[Andrew Poulsom]

Do you really have P1, P2 and P3 instead of Phase 1, Phase 2 and Phase 3?

 

[Andrew Poulsom]

If you change the entries in A2:A4 to P1, P2 and P3, this formula returns what you have in B2:C4:

=IF(SUMPRODUCT((($G$4:$M$15="Yes")+($G$4:$M$15="N/A"))*($E$4:$E$15=$A2)*($G$3:$M$3=B$1))=(COUNTIF(G$3:M$3,B$1)*COUNTIF($E$4:$E$15,$A2)),"Yes","No")

 

[gloop]

This is great. Thanks again for your help Andrew!