Complex count and lookup criteria with multiple values - SumProduct???

[AgentoUK]

Following on from a question I asked a few days ago, I need to calculate the below

I have a control Table, indicating whether each Team should be included in the results

	A	B
1	Team	Include
2	Team Alpha	Yes
3	Team Bravo	No
4	Team Charlie	Yes
5	Team Delta	No

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I then have a register of each user, and wether they were in or not on a particular day

	A	B	C	D	E	F	G	E
1	Agent Name	Team	01/01/2018	02/01/2018	03/01/2018	04/01/2018	05/01/2018	Total
2	John	Team Bravo		In		In		0
3	Mary	Team Alpha		In	In	In	In	4
4	Kate	Team Delta	In	In	In	In	In	0
5	Brian	Team Bravo	In		In		In	0
6	Mark	Team Alpha	In		In	In	In	3
7	Sarah	Team Charlie	In	In	In	In	In	5
8		Total	2	2	2	3	3
9
10		Overall Total	12

<colgroup><col><col span="2"><col span="6"></colgroup><tbody>
</tbody>

So I'd like to have a formula to calculate the volume of "In" values (for each row, column and overall) Only for teams where the Team Name in the control table has a value of "Yes" for the Include column.

Can anyone help?

 

[Scott T]

Book1
	A	B
1	Team	Include
2	Team Alpha	Yes
3	Team Bravo	No
4	Team Charlie	Yes
5	Team Delta	No
Sheet1

Book1
	A	B	C	D	E	F	G	H
1	Agent Name	Team	1/1/2018	2/1/2018	3/1/2018	4/1/2018	5/1/2018	Total
2	John	Team Bravo		In		In		0
3	Mary	Team Alpha		In	In	In	In	4
4	Kate	Team Delta	In	In	In	In	In	0
5	Brian	Team Bravo	In		In		In	0
6	Mark	Team Alpha	In		In	In	In	4
7	Sarah	Team Charlie	In	In	In	In	In	5
8		Total	2	2	3	3	3
9
10		Overall Total	13
Sheet2

Cell Formulas
Range	Formula
H2	=SUMPRODUCT(--(Sheet1!$A$2:$A$5=Sheet2!B2)*--(Sheet1!$B$2:$B$5="yes"))*COUNTIF(C2:G2,"in")
H3	=SUMPRODUCT(--(Sheet1!$A$2:$A$5=Sheet2!B3)*--(Sheet1!$B$2:$B$5="yes"))*COUNTIF(C3:G3,"in")
H4	=SUMPRODUCT(--(Sheet1!$A$2:$A$5=Sheet2!B4)*--(Sheet1!$B$2:$B$5="yes"))*COUNTIF(C4:G4,"in")
H5	=SUMPRODUCT(--(Sheet1!$A$2:$A$5=Sheet2!B5)*--(Sheet1!$B$2:$B$5="yes"))*COUNTIF(C5:G5,"in")
H6	=SUMPRODUCT(--(Sheet1!$A$2:$A$5=Sheet2!B6)*--(Sheet1!$B$2:$B$5="yes"))*COUNTIF(C6:G6,"in")
H7	=SUMPRODUCT(--(Sheet1!$A$2:$A$5=Sheet2!B7)*--(Sheet1!$B$2:$B$5="yes"))*COUNTIF(C7:G7,"in")
C10	=SUM(C8:G8)
C8	{=SUMPRODUCT(IFNA(MATCH($B$2:$B$7&"yes"&C2:C7,Sheet1!$A$2:$A$5&Sheet1!$B$2:$B$5&"in",0),-1)-IFNA(MATCH($B$2:$B$7&"yes"&C2:C7,Sheet1!$A$2:$A$5&Sheet1!$B$2:$B$5&"in",0),0)+1)}
D8	{=SUMPRODUCT(IFNA(MATCH($B$2:$B$7&"yes"&D2:D7,Sheet1!$A$2:$A$5&Sheet1!$B$2:$B$5&"in",0),-1)-IFNA(MATCH($B$2:$B$7&"yes"&D2:D7,Sheet1!$A$2:$A$5&Sheet1!$B$2:$B$5&"in",0),0)+1)}
E8	{=SUMPRODUCT(IFNA(MATCH($B$2:$B$7&"yes"&E2:E7,Sheet1!$A$2:$A$5&Sheet1!$B$2:$B$5&"in",0),-1)-IFNA(MATCH($B$2:$B$7&"yes"&E2:E7,Sheet1!$A$2:$A$5&Sheet1!$B$2:$B$5&"in",0),0)+1)}
F8	{=SUMPRODUCT(IFNA(MATCH($B$2:$B$7&"yes"&F2:F7,Sheet1!$A$2:$A$5&Sheet1!$B$2:$B$5&"in",0),-1)-IFNA(MATCH($B$2:$B$7&"yes"&F2:F7,Sheet1!$A$2:$A$5&Sheet1!$B$2:$B$5&"in",0),0)+1)}
G8	{=SUMPRODUCT(IFNA(MATCH($B$2:$B$7&"yes"&G2:G7,Sheet1!$A$2:$A$5&Sheet1!$B$2:$B$5&"in",0),-1)-IFNA(MATCH($B$2:$B$7&"yes"&G2:G7,Sheet1!$A$2:$A$5&Sheet1!$B$2:$B$5&"in",0),0)+1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jtakw]

Hi,

If I understand correctly, another way (a little simpler):

Book1
	A	B	C	D	E	F	G	H
1	Team	Include
2	Team Alpha	Yes
3	Team Bravo	No
4	Team Charlie	Yes
5	Team Delta	No
6
7
8	Agent Name	Team	1/1/2018	2/1/2018	3/1/2018	4/1/2018	5/1/2018	Total
9	John	Team Bravo		In		In		0
10	Mary	Team Alpha		In	In	In	In	4
11	Kate	Team Delta	In	In	In	In	In	0
12	Brian	Team Bravo	In		In		In	0
13	Mark	Team Alpha	In		In	In	In	4
14	Sarah	Team Charlie	In	In	In	In	In	5
15		Total	2	2	3	3	3
16
17		Overall Total	13
Sheet274

Cell Formulas
Range	Formula
H9	=IF(VLOOKUP(B9,A$2:B$5,2,0)="Yes",COUNTIF(C9:G9,"In"),0)
C15	=COUNTIFS(C9:C14,"In",$H9:$H14,"<>0")
C17	=SUM(C15:G15)

H9 formula copied down to H14
C15 formula copied across to G15