SUMPRODUCT with Date Range and a Criteria

I want the count of days that each person has worked. B1 is where I would type in the name of the employee and the function would tell me how many days he has worked based on the perimeter provided (<=C5 (July 31) and >=D5 (May 1st)). I created the function below but got an error (#N/A). Please Help!! Thank you!



1) =SUMPRODUCT((BASE_PBI_01_21!$CP$2:$CP$10000=$B$1)*(BASE_PBI_01_21!$BZ$2:$BZ$100000<=$C$5)*(BASE_PBI_01_21!$BZ$2:$BZ$100000>=$D$5)) -->Error (#N/A)
2) =SUMPRODUCT((BASE_PBI_01_21!$CP$2:$CP$10000=$B$1),(BASE_PBI_01_21!$BZ$2:$BZ$100000<=$C$5),(BASE_PBI_01_21!$BZ$2:$BZ$100000>=$D$5)) --> Error (#Value!)


-DH

The second approach won't work because you are not "co-ercing" the TRUE/FALSE arrays

The first approach is correct syntactically but you need all ranges the same size (some ranges end at row 10,000 some at row 100,000)

In Excel 2007 probably better to use COUNTIFS like this:

=COUNTIFS(BASE_PBI_01_21!$CP:$CP,$B$1,BASE_PBI_01_21!$BZ:$BZ,"<="&$C$5,BASE_PBI_01_21!$BZ:$BZ,">="&$D$5)