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

AgentoUK

New Member
Joined
Sep 14, 2018
Messages
2
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?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Book1
AB
1TeamInclude
2Team AlphaYes
3Team BravoNo
4Team CharlieYes
5Team DeltaNo
Sheet1



Book1
ABCDEFGH
1Agent NameTeam1/1/20182/1/20183/1/20184/1/20185/1/2018Total
2JohnTeam BravoInIn0
3MaryTeam AlphaInInInIn4
4KateTeam DeltaInInInInIn0
5BrianTeam BravoInInIn0
6MarkTeam AlphaInInInIn4
7SarahTeam CharlieInInInInIn5
8Total22333
9
10Overall Total13
Sheet2
Cell Formulas
RangeFormula
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.
 
Upvote 0
Hi,

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


Book1
ABCDEFGH
1TeamInclude
2Team AlphaYes
3Team BravoNo
4Team CharlieYes
5Team DeltaNo
6
7
8Agent NameTeam1/1/20182/1/20183/1/20184/1/20185/1/2018Total
9JohnTeam BravoInIn0
10MaryTeam AlphaInInInIn4
11KateTeam DeltaInInInInIn0
12BrianTeam BravoInInIn0
13MarkTeam AlphaInInInIn4
14SarahTeam CharlieInInInInIn5
15Total22333
16
17Overall Total13
Sheet274
Cell Formulas
RangeFormula
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
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top