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

1	A	B	C	D	E
2	Friday, 11 January 2019 <tbody> </tbody>	Phillip Watson <tbody> </tbody>	Mary Woodson <tbody> </tbody>	Phillip Watson <tbody> </tbody>	Mary Woodson <tbody> </tbody>
3	Saturday, 12 January 2019 <tbody> </tbody>	Phillip Watson <tbody> </tbody>	Mary Woodson <tbody> </tbody>	Phillip Watson <tbody> </tbody>	Mary Woodson <tbody> </tbody>
4	Sunday, 13 January 2019 <tbody> </tbody>	Phillip Watson	Mary Woodson	Phillip Watson	Mary Woodson
5	Monday, 14 January 2019 <tbody> </tbody>	Phillip Watson	Phillip Watson	Mary Woodson	Mary Woodson

<tbody>
</tbody>

<tbody>
</tbody>

passes sheet

1	A	B	C	D	E
2	Phillip Watson	Friday, 11 January 2019	Friday, 12 January 2019	xxxxxx	xxxxxx
3
4
5

<tbody>
</tbody>

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.