Thread: Trying to count the amount of times a certain date falls between a range for a specif

Hello, I have a situation here where I have Sheet 1 and in column A where I have multiple names of individuals (Ex A2 Tom Jones, A3 Bob Villa). On Sheet 2 I also have the names of these individuals followed by multiple days that they took off in a given year (ex. A2 Tom Jones, B2 7/1/2011, C2 7/23/2011). I would like to find a formula that would count the number of times an individual has a date listed between a certain range. I have tried a countif and also a sum product and neither have worked. Here are the formulas I have used so far and they were typed on Sheet 1 (O1 and P1 have the dates 7/1/2011 and 7/31/2011 respectively):
=COUNTIFS(A2,Sheet2!A1:BB53,Sheet2!A1:BB53,">="&O1,Sheet2!A1:BB53,"<="&P1) -- I need to have the Ranges on Sheet2 portion of the formula because Tom Jones will now necessarily be in the same row on Sheet1 and Sheet2
=SUMPRODUCT((Sheet2!A1:A35=A6),(Sheet2!B1:AJ35>=O1),(Sheet2!B1:AJ35<=P1))

1.JPG2.JPG

Try:

=SUMPRODUCT((Sheet2!A1:A35=A6)*(Sheet2!B1:AJ35>=O1)*(Sheet2!B1:AJ35<=P1))

It worked, thank you!