Count Number of Rep's

[reyrey]

I've got a list of people working throughout the day on Sheet1


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Start Day and Time[/TD]
[TD="align: center"]End Day and Time[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jane Doe[/TD]
[TD]11/01/2017 07:00[/TD]
[TD]11/01/2017 12:00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]John Doe [/TD]
[TD]11/01/2017 08:00[/TD]
[TD]11/01/2017 10:00[/TD]
[/TR]
</tbody>[/TABLE]




and would like to convert data into a timeline spreadsheet below on Sheet2. I just seem to get the code correct, I'm using the following formula in cell B1. But I not getting the end result, any idea what I'm missing?

=SUMPRODUCT(A1>=SHEET1!B:B)*(A2<=SHEET1!C:C)


[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]11/01/2017 07:00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11/01/2017 07:30[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]11/01/2017 08:00[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]11/01/2017 08:30[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]11/01/2017 09:00[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]11/01/2017 09:30[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]11/01/2017 10:00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]11/01/2017 10:30[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]11/01/2017 11:00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]11/01/2017 11:30[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]11/01/2017 12:00[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

Your function SUMPRODUCT needs another set of parentheses, I think.

=SUMPRODUCT( (A1>=SHEET1!B:B)*(A2<=SHEET1!C:C) )​