Formula Help needed

[Fraserj]

Hello People, I'm stuck and need a little help if possible.

I need a Formula which Picks up a name from Range H:I (Appearances or substitutions) But, (With Michael being 'Sheet1'!B2, Lee being 'Sheet1'!B3...etc...) Only picks up these names from Column H and I if, Column A = "EFC" have kept a clean sheet "0" in Column D) or Column E = "EFC" have kept a clean sheet ("0") in Column B

So in turn, results should look like (As Jack didn't play in one of the games, or make a sub appearance)[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Michael[/TD]
[TD]Lee[/TD]
[TD]Jack[/TD]
[TD]Oscar[/TD]
[TD]Harry[/TD]
[/TR]
[TR]
[TD]Clean Sheets[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3
[/TD]
[/TR]
</tbody>[/TABLE]



The table below shows a list of Results (Not actual grid references, but I can hopefully edit if problem is sorted) which are on Sheet 2 of my spreadsheet.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Home[/TD]
[TD]Score[/TD]
[TD]-[/TD]
[TD]Score[/TD]
[TD]Away[/TD]
[TD]Scorers<strike></strike>[/TD]
[TD]Assists<strike></strike>[/TD]
[TD]Starting Team[/TD]
[TD]Substitute[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]EFC[/TD]
[TD]2[/TD]
[TD]-[/TD]
[TD]0[/TD]
[TD]Liverpool[/TD]
[TD]Lee, Lee[/TD]
[TD]Jack[/TD]
[TD]Michael, Jack, Lee[/TD]
[TD]Harry, Oscar[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]EFC[/TD]
[TD]0[/TD]
[TD]-[/TD]
[TD]2[/TD]
[TD]Manchester[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jack, Lee, Oscar[/TD]
[TD]Harry, Michael[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Liverpool[/TD]
[TD]0[/TD]
[TD]-[/TD]
[TD]1[/TD]
[TD]EFC[/TD]
[TD]Oscar[/TD]
[TD]Harry[/TD]
[TD]Lee, Harry, Oscar[/TD]
[TD]Michael, Jack[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Manchester[/TD]
[TD]0[/TD]
[TD]-[/TD]
[TD]1[/TD]
[TD]EFC[/TD]
[TD][/TD]
[TD][/TD]
[TD]Harry, Oscar, Michael[/TD]
[TD]Lee[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]EFC[/TD]
[TD]2[/TD]
[TD]-[/TD]
[TD]1[/TD]
[TD]Manchester[/TD]
[TD]Michael, Michael[/TD]
[TD]Jack[/TD]
[TD]Lee, Harry, Michael[/TD]
[TD]Oscar, Jack[/TD]
[/TR]
</tbody>[/TABLE]



I have a table on Worksheet 1 that comes up something like

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Michael[/TD]
[TD]Lee[/TD]
[TD]Jack[/TD]
[TD]Oscar[/TD]
[TD]Harry[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Appearances[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Goals[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Assists[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Clean Sheets[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I have formulas for How many goals, Appearances and Assists, but I can't figure out how to sort clean sheets out If they made an appearance in that game


To calculate the goals I have used the formula below, which also works with adjustment to cell ranges for Appearances and Assists.
Code:
=SUM((LEN(F3:F10000)-LEN(SUBSTITUTE(UPPER(F3:F10000), UPPER('Sheet1'!C3), "")))/LEN('Sheet1'!C3))
To calculate clean sheets I used the formula below
Code:
=COUNTIFS('Sheet2'!B:B,"EFC",'Sheet2'!E:E,"0")+COUNTIFS('Sheet2'!F:F,"EFC",'Sheet2'!C:C,"0

Sorry if this post is Longer than it needed to be, I didn't know if the other Formulas may help you come to a conclusion quicker and thought they may be helpful

Thank you in advance

 

[Fraserj]

Anybody?