bobbysmith
New Member
- Joined
- Jan 24, 2015
- Messages
- 29
Was hope someone can assist with my formula. I cant understand why results are not showing up on 19:00 and end at 22:00 (Col H is just the intervals). I seems to shift down to start at 19:30 and end at 22:30 instead of starting at 19:00 and ending at 22:00 (start 1900-1930, end 2130-2200). Any insight is greatly appreciated.
Col A Col B Col C
<tbody>
</tbody>
=SUMPRODUCT(($A$2<=F1)*($B$2>F1)*$C$2)
F H I
<tbody>
</tbody>
Col A Col B Col C
start | stop | slot |
19:00 | 22:00 | 5 |
<tbody>
</tbody>
=SUMPRODUCT(($A$2<=F1)*($B$2>F1)*$C$2)
F H I
6:00 | 6:00-6:30 | 0 | |
6:30 | 6:30-7:00 | 0 | |
7:00 | 7:00-7:30 | 0 | |
7:30 | 7:30-8:00 | 0 | |
8:00 | 8:00-8:30 | 0 | |
8:30 | 8:30-9:00 | 0 | |
9:00 | 9:00-9:30 | 0 | |
9:30 | 9:30-10:00 | 0 | |
10:00 | 10:00-10:30 | 0 | |
10:30 | 10:30-11:00 | 0 | |
11:00 | 11:00-11:30 | 0 | |
11:30 | 11:30-12:00 | 0 | |
12:00 | 12:00-12:30 | 0 | |
12:30 | 12:30-13:00 | 0 | |
13:00 | 13:00-13:30 | 0 | |
13:30 | 13:30-14:00 | 0 | |
14:00 | 14:00-14:30 | 0 | |
14:30 | 14:30-15:00 | 0 | |
15:00 | 15:00-15:30 | 0 | |
15:30 | 15:30-16:00 | 0 | |
16:00 | 16:00-16:30 | 0 | |
16:30 | 16:30-17:00 | 0 | |
17:00 | 17:00-17:30 | 0 | |
17:30 | 17:30-18:00 | 0 | |
18:00 | 18:00-18:30 | 0 | |
18:30 | 18:30-19:00 | 0 | |
19:00 | 19:00-19:30 | 0 | |
19:30 | 19:30-20:00 | 5 | |
20:00 | 20:00-20:30 | 5 | |
20:30 | 20:30-21:00 | 5 | |
21:00 | 21:00-21:30 | 5 | |
21:30 | 21:30-22:00 | 5 | |
22:00 | 22:00-22:30 | 5 | |
22:30 | 22:30-23:00 | 0 | |
23:00 | 23:00- | 0 |
<tbody>
</tbody>