SUMIFS Between Date Range

[betapiper]

Probably a repeat question but i just cant figure this out.

Im trying to sum the number of times a persons name appears in a range on my "classes" sheet if the date in column A is between a range of dates in my "passes" sheet.

Apologies for the terrible formatting of my tables im not familiar with the sticky interface.

classes sheet

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 173"]
<tbody>[TR="class: grid"]
[TD="width: 173, align: right"]Friday, 11 January 2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR="class: grid"]
[TD="width: 130"]Phillip Watson[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR="class: grid"]
[TD="width: 130"]Mary Woodson[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR="class: grid"]
[TD="width: 130"]Phillip Watson[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR="class: grid"]
[TD="width: 130"]Mary Woodson[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 173"]
<tbody>[TR="class: grid"]
[TD="width: 173, align: right"]Saturday, 12 January 2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR="class: grid"]
[TD="width: 130"]Phillip Watson[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR="class: grid"]
[TD="width: 130"]Mary Woodson[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR="class: grid"]
[TD="width: 130"]Phillip Watson[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR="class: grid"]
[TD="width: 130"]Mary Woodson[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 173, align: left"]
<tbody>[TR="class: grid"]
[TD="width: 173, align: right"]Sunday, 13 January 2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Phillip Watson[/TD]
[TD]Mary Woodson[/TD]
[TD]Phillip Watson[/TD]
[TD]Mary Woodson[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 173"]
<tbody>[TR="class: grid"]
[TD="width: 173, align: right"]Monday, 14 January 2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Phillip Watson[/TD]
[TD]Phillip Watson[/TD]
[TD]Mary Woodson[/TD]
[TD]Mary Woodson[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 757"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

passes sheet

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Phillip Watson[/TD]
[TD]Friday, 11 January 2019[/TD]
[TD]Friday, 12 January 2019[/TD]
[TD]xxxxxx[/TD]
[TD]xxxxxx[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]









So essentially what i want to do in the "passes" D2 is sum the number of times Phillip Watson appears within "classes" B2:E5 if the date in "classes" A:A is between "passes" B2:C2

I was trying this but getting nowhere:
=SUMIFS(Classes!$C$2:$V$1000,Classes!A:A,>=Passes!C2,Classes!$A$2:$A$1000,<=Passes!D2)

Thanks

 

[steve the fish]

See if this works:

=SUMPRODUCT((classes!$B$2:$E$5=A2)*(classes!$A$2:$A$5>=B2)*(classes!$A$2:$A$5<=C2))

 

[betapiper]

Sorry for the late reply, xmas and all.

Thanks for the tip. i got it to work with the below:
=SUMPRODUCT((Classes!$B$2:$E$5=A2)*((Classes!$A$2:$A$5>=C2)+(Classes!$A$2:$A$5<=C2)))

Cheers,

 

[steve the fish]

What you have there cant work for what you asked for. For your test data it gives result of 10. From what i can see it should be 4.