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]

With your first table in the range A1:C4 and your second table in the range E1:J6, try in B2 copied down and across:

=IF(SUMPRODUCT(--(INDEX($F$4:$J$6,MATCH($A2,$E$4:$E$6,FALSE),0)="Yes"),--($F$2:$J$2=B$1))=COUNTIF(F$2:J$2,B$1),"Yes","No")

If you have Excel 2007 or above you can use COUNTIFS insead of SUMPRODUCT.

 

[gloop]

With your first table in the range A1:C4 and your second table in the range E1:J6, try in B2 copied down and across:

=IF(SUMPRODUCT(--(INDEX($F$4:$J$6,MATCH($A2,$E$4:$E$6,FALSE),0)="Yes"),--($F$2:$J$2=B$1))=COUNTIF(F$2:J$2,B$1),"Yes","No")

If you have Excel 2007 or above you can use COUNTIFS insead of SUMPRODUCT.

I've put this in - but it's coming up with a resource error and evaluates to 0. I should note that cells F4 to J6 are set to "Yes" or "No" based on another formula.

I'm on Excel 2010, so could use COUNTIFS ?

Thanks for the fast reply!

 

[Andrew Poulsom]

What's a resource error?

 

[gloop]

What's a resource error?

Resolved this...
Formula works now! (Please ignore below!!)

THANKS ANDREW.

One further complication that I can't determine, however. If I add another dimension to it, i.e. repeat "Phase 3" twice, it won't count all instances of Task 2 Action1 for all instances of Phase 3. Does this make sense? It actually evaluates to "Yes" in these scenarios. E.g., formula to calculate the below. Highlighted the hard bit...

Any ideas? Again, much thanks for your help!

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

<TBODY>
</TBODY>

 

[gloop]

One further complication that I can't determine, however. If I add another dimension to it, i.e. repeat "Phase 3" twice, it won't count all instances of Task 2 Action1 for all instances of Phase 3. Does this make sense? It actually evaluates to "Yes" in these scenarios. E.g., formula to calculate the below. Highlighted the hard bit... Seems it works if the first row is marked as Yes (i.e. it doesn't look at the 2nd row). Presume this is a limitation of the MATCH() function?

Any ideas? Again, much thanks for your help!

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

<TBODY>
</TBODY>

 

[Andrew Poulsom]

Does this work for you?

=IF(SUMPRODUCT(($F$4:$J$7="Yes")*($E$4:$E$7=$A2)*($F$2:$J$2=B$1))=(COUNTIF(F$2:J$2,B$1)*COUNTIF($E$4:$E$7,$A2)),"Yes","No")

 

[gloop]

Unfortunately not - seems the first half of the formula evaluates correctly when I step into the steps (sumproduct), but the second half is a little off. I'm stumped.

 

[Andrew Poulsom]

What do you mean by "the second half is a little off"? Can you give an example where the formula doesn't work?

 

[gloop]

In the below table the formula does not work. (added a "person" to make this make more sense - but basically either the tasks are done, not done, or not applicable, the person is not important when looking at the overall actions.) I think you formula would be

=IF(SUMPRODUCT(($G$4:$M$15="Yes")*($E$4:$E$15=$A2)*($G$2:$M$2=B$1))=(COUNTIF(G$2:M$2,B$1)*COUNTIF($E$4:$E$15,$A2)),"Yes","No")

in this scenario, but it evaluates to Yes for each.

Phases

Task 1

Task 2

Phase 1

No

No

Action 1

Action 1

Action 1

Action 2

Action 2

Action 2

Action 2

Phase 2

Yes

Yes

Phase

Person

Task 1

Task 2

Task 3

Task 1

Task 2

Task 3

Task 4

Phase 3

No

Yes

P1

Bob

Yes

No

No

N/A

N/A

N/A

N/A

P1

John

No

No

No

N/A

N/A

N/A

N/A

P1

Chris

N/A

N/A

N/A

Yes

Yes

Yes

Yes

P1

Jane

N/A

N/A

N/A

No

Yes

Yes

Yes

P2

Bob

Yes

Yes

Yes

N/A

N/A

N/A

N/A

P2

John

Yes

Yes

Yes

N/A

N/A

N/A

N/A

P2

Chris

N/A

N/A

N/A

Yes

Yes

Yes

Yes

P2

Jane

N/A

N/A

N/A

Yes

Yes

Yes

Yes

P3

Bob

No

Yes

No

N/A

N/A

N/A

N/A

P3

John

No

Yes

Yes

N/A

N/A

N/A

N/A

P3

Chris

N/A

N/A

N/A

Yes

Yes

Yes

Yes

P3

Jane

N/A

N/A

N/A

Yes

Yes

Yes

Yes

<tbody>
</tbody><colgroup><col><col><col><col><col span="5"><col span="4"></colgroup>