Count Number of Rep's

[reyrey]

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

	A	B	C
1	Name	Start Day and Time	End Day and Time
2	Jane Doe	11/01/2017 07:00	11/01/2017 12:00
3	John Doe	11/01/2017 08:00	11/01/2017 10:00

<tbody>
</tbody>

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)

	A	B
1	11/01/2017 07:00	1
2	11/01/2017 07:30	1
3	11/01/2017 08:00	2
4	11/01/2017 08:30	2
5	11/01/2017 09:00	2
6	11/01/2017 09:30	2
7	11/01/2017 10:00	1
8	11/01/2017 10:30	1
9	11/01/2017 11:00	1
10	11/01/2017 11:30	1
11	11/01/2017 12:00	0

<tbody>
</tbody>

 

[DRSteele]

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

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