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

[TABLE="class: outer_border, width: 250"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]Team
[/TD]
[TD="align: center"]Include[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Team Alpha[/TD]
[TD="align: center"]Yes[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Team Bravo[/TD]
[TD="align: center"]No
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]Team Charlie
[/TD]
[TD="align: center"]Yes
[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Team Delta[/TD]
[TD="align: center"]No[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: outer_border, width: 647"]
<colgroup><col><col span="2"><col span="6"></colgroup><tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]E
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]Agent Name
[/TD]
[TD="align: center"]Team
[/TD]
[TD="align: center"]01/01/2018
[/TD]
[TD="align: center"]02/01/2018[/TD]
[TD="align: center"]03/01/2018[/TD]
[TD="align: center"]04/01/2018[/TD]
[TD="align: center"]05/01/2018[/TD]
[TD="align: center"]Total
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]Team Bravo
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]In[/TD]
[TD="align: center"][/TD]
[TD="align: center"]In[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Mary[/TD]
[TD="align: center"]Team Alpha[/TD]
[TD="align: center"][/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Kate[/TD]
[TD="align: center"]Team Delta
[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Brian[/TD]
[TD="align: center"]Team Bravo[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"][/TD]
[TD="align: center"]In[/TD]
[TD="align: center"][/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Mark[/TD]
[TD="align: center"]Team Alpha[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"][/TD]
[TD="align: center"]In [/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Sarah[/TD]
[TD="align: center"]Team Charlie
[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]5
[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Total
[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Overall Total
[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

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