Newbie987654321
New Member
- Joined
- Mar 13, 2018
- Messages
- 9
Hi Guys,
I need your help. I need to know how many artists in total are in within a time range.
I need a formula to put into table 2 to get the number of artists. I just did this manually. LOL
I tried SUMPRODUCT but it will only give me the number on artists on based on their Time In, but I actually need the total number of artist until their Time Out.
I tried this but it was no good.
=SUMPRODUCT(--(HOUR($E$3:$E$18)>=H3),--(HOUR($E$3:$E$18)<i3))
Here's the table.
[TABLE="width: 476"]
<tbody>[TR]
[TD]TABLE 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TABLE 2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Artist
[/TD]
[TD]Time in
[/TD]
[TD]Time Out
[/TD]
[TD][/TD]
[TD="colspan: 2"]Time in Duration
[/TD]
[TD]Artists
[/TD]
[/TR]
[TR]
[TD]Sam Smith
[/TD]
[TD]9:00 PM
[/TD]
[TD]6:00 AM
[/TD]
[TD][/TD]
[TD]9:00 PM
[/TD]
[TD]9:30 PM
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Justin Bieber
[/TD]
[TD]9:00 PM
[/TD]
[TD]6:00 AM
[/TD]
[TD][/TD]
[TD]9:30 PM
[/TD]
[TD]10:00 PM
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Selena Gomez
[/TD]
[TD]9:30 PM
[/TD]
[TD]6:30 AM
[/TD]
[TD][/TD]
[TD]10:00 PM
[/TD]
[TD]10:30 PM
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Britney Spears
[/TD]
[TD]10:00 PM
[/TD]
[TD]7:00 AM
[/TD]
[TD][/TD]
[TD]10:30 PM
[/TD]
[TD]11:00 PM
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Christina Aguilera
[/TD]
[TD]10:45 PM
[/TD]
[TD]7:45 AM
[/TD]
[TD][/TD]
[TD]11:00 PM
[/TD]
[TD]11:30 PM
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Elle King
[/TD]
[TD]11:45 PM
[/TD]
[TD]8:45 AM
[/TD]
[TD][/TD]
[TD]11:30 PM
[/TD]
[TD]12:00 AM
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]Miley Cyrus
[/TD]
[TD]11:45 PM
[/TD]
[TD]8:45 AM
[/TD]
[TD][/TD]
[TD]12:00 AM
[/TD]
[TD]12:30 AM
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]Angelina Jolie
[/TD]
[TD]12:00 AM
[/TD]
[TD]9:00 AM
[/TD]
[TD][/TD]
[TD]12:30 AM
[/TD]
[TD]1:00 AM
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]Brad Pitt
[/TD]
[TD]1:00 AM
[/TD]
[TD]10:00 AM
[/TD]
[TD][/TD]
[TD]1:00 AM
[/TD]
[TD]1:30 AM
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]Justin Timberlake
[/TD]
[TD]2:00 AM
[/TD]
[TD]11:00 AM
[/TD]
[TD][/TD]
[TD]1:30 AM
[/TD]
[TD]2:00 AM
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]Pink
[/TD]
[TD]3:00 AM
[/TD]
[TD]12:00 PM
[/TD]
[TD][/TD]
[TD]2:00 AM
[/TD]
[TD]2:30 AM
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]Amy Lee
[/TD]
[TD]3:00 AM
[/TD]
[TD]12:00 PM
[/TD]
[TD][/TD]
[TD]2:30 AM
[/TD]
[TD]3:00 AM
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3:00 AM
[/TD]
[TD]3:30 AM
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3:30 AM
[/TD]
[TD]4:00 AM
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4:00 AM
[/TD]
[TD]4:30 AM
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4:30 AM
[/TD]
[TD]5:00 AM
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5:00 AM
[/TD]
[TD]5:30 AM
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5:30 AM
[/TD]
[TD]6:00 AM
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6:00 AM
[/TD]
[TD]6:30 AM
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6:30 AM
[/TD]
[TD]7:00 AM
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7:00 AM
[/TD]
[TD]7:30 AM
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7:30 AM
[/TD]
[TD]8:00 AM
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8:00 AM
[/TD]
[TD]8:30 AM
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8:30 AM
[/TD]
[TD]9:00 AM
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9:00 AM
[/TD]
[TD]9:30 AM
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9:30 AM
[/TD]
[TD]10:00 AM
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10:00 AM
[/TD]
[TD]10:30 AM
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10:30 AM
[/TD]
[TD]11:00 AM
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11:00 AM
[/TD]
[TD]11:30 AM
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11:30 AM
[/TD]
[TD]12:00 PM
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]
</i3))
I need your help. I need to know how many artists in total are in within a time range.
I need a formula to put into table 2 to get the number of artists. I just did this manually. LOL
I tried SUMPRODUCT but it will only give me the number on artists on based on their Time In, but I actually need the total number of artist until their Time Out.
I tried this but it was no good.
=SUMPRODUCT(--(HOUR($E$3:$E$18)>=H3),--(HOUR($E$3:$E$18)<i3))
Here's the table.
[TABLE="width: 476"]
<tbody>[TR]
[TD]TABLE 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TABLE 2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Artist
[/TD]
[TD]Time in
[/TD]
[TD]Time Out
[/TD]
[TD][/TD]
[TD="colspan: 2"]Time in Duration
[/TD]
[TD]Artists
[/TD]
[/TR]
[TR]
[TD]Sam Smith
[/TD]
[TD]9:00 PM
[/TD]
[TD]6:00 AM
[/TD]
[TD][/TD]
[TD]9:00 PM
[/TD]
[TD]9:30 PM
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Justin Bieber
[/TD]
[TD]9:00 PM
[/TD]
[TD]6:00 AM
[/TD]
[TD][/TD]
[TD]9:30 PM
[/TD]
[TD]10:00 PM
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Selena Gomez
[/TD]
[TD]9:30 PM
[/TD]
[TD]6:30 AM
[/TD]
[TD][/TD]
[TD]10:00 PM
[/TD]
[TD]10:30 PM
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Britney Spears
[/TD]
[TD]10:00 PM
[/TD]
[TD]7:00 AM
[/TD]
[TD][/TD]
[TD]10:30 PM
[/TD]
[TD]11:00 PM
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Christina Aguilera
[/TD]
[TD]10:45 PM
[/TD]
[TD]7:45 AM
[/TD]
[TD][/TD]
[TD]11:00 PM
[/TD]
[TD]11:30 PM
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Elle King
[/TD]
[TD]11:45 PM
[/TD]
[TD]8:45 AM
[/TD]
[TD][/TD]
[TD]11:30 PM
[/TD]
[TD]12:00 AM
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]Miley Cyrus
[/TD]
[TD]11:45 PM
[/TD]
[TD]8:45 AM
[/TD]
[TD][/TD]
[TD]12:00 AM
[/TD]
[TD]12:30 AM
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]Angelina Jolie
[/TD]
[TD]12:00 AM
[/TD]
[TD]9:00 AM
[/TD]
[TD][/TD]
[TD]12:30 AM
[/TD]
[TD]1:00 AM
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]Brad Pitt
[/TD]
[TD]1:00 AM
[/TD]
[TD]10:00 AM
[/TD]
[TD][/TD]
[TD]1:00 AM
[/TD]
[TD]1:30 AM
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]Justin Timberlake
[/TD]
[TD]2:00 AM
[/TD]
[TD]11:00 AM
[/TD]
[TD][/TD]
[TD]1:30 AM
[/TD]
[TD]2:00 AM
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]Pink
[/TD]
[TD]3:00 AM
[/TD]
[TD]12:00 PM
[/TD]
[TD][/TD]
[TD]2:00 AM
[/TD]
[TD]2:30 AM
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]Amy Lee
[/TD]
[TD]3:00 AM
[/TD]
[TD]12:00 PM
[/TD]
[TD][/TD]
[TD]2:30 AM
[/TD]
[TD]3:00 AM
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3:00 AM
[/TD]
[TD]3:30 AM
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3:30 AM
[/TD]
[TD]4:00 AM
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4:00 AM
[/TD]
[TD]4:30 AM
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4:30 AM
[/TD]
[TD]5:00 AM
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5:00 AM
[/TD]
[TD]5:30 AM
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5:30 AM
[/TD]
[TD]6:00 AM
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6:00 AM
[/TD]
[TD]6:30 AM
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6:30 AM
[/TD]
[TD]7:00 AM
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7:00 AM
[/TD]
[TD]7:30 AM
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7:30 AM
[/TD]
[TD]8:00 AM
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8:00 AM
[/TD]
[TD]8:30 AM
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8:30 AM
[/TD]
[TD]9:00 AM
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9:00 AM
[/TD]
[TD]9:30 AM
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9:30 AM
[/TD]
[TD]10:00 AM
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10:00 AM
[/TD]
[TD]10:30 AM
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10:30 AM
[/TD]
[TD]11:00 AM
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11:00 AM
[/TD]
[TD]11:30 AM
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11:30 AM
[/TD]
[TD]12:00 PM
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]
</i3))