Countif within time range

[Newbie987654321]

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 1				TABLE 2
Artist	Time in	Time Out		Time in Duration	Artists
Sam Smith	9:00 PM	6:00 AM		9:00 PM	9:30 PM	2
Justin Bieber	9:00 PM	6:00 AM		9:30 PM	10:00 PM	3
Selena Gomez	9:30 PM	6:30 AM		10:00 PM	10:30 PM	4
Britney Spears	10:00 PM	7:00 AM		10:30 PM	11:00 PM	5
Christina Aguilera	10:45 PM	7:45 AM		11:00 PM	11:30 PM	5
Elle King	11:45 PM	8:45 AM		11:30 PM	12:00 AM	7
Miley Cyrus	11:45 PM	8:45 AM		12:00 AM	12:30 AM	8
Angelina Jolie	12:00 AM	9:00 AM		12:30 AM	1:00 AM	8
Brad Pitt	1:00 AM	10:00 AM		1:00 AM	1:30 AM	9
Justin Timberlake	2:00 AM	11:00 AM		1:30 AM	2:00 AM	9
Pink	3:00 AM	12:00 PM		2:00 AM	2:30 AM	10
Amy Lee	3:00 AM	12:00 PM		2:30 AM	3:00 AM	10
				3:00 AM	3:30 AM	12
				3:30 AM	4:00 AM	12
				4:00 AM	4:30 AM	12
				4:30 AM	5:00 AM	12
				5:00 AM	5:30 AM	12
				5:30 AM	6:00 AM	12
				6:00 AM	6:30 AM	10
				6:30 AM	7:00 AM	9
				7:00 AM	7:30 AM	8
				7:30 AM	8:00 AM	7
				8:00 AM	8:30 AM	7
				8:30 AM	9:00 AM	5
				9:00 AM	9:30 AM	4
				9:30 AM	10:00 AM	4
				10:00 AM	10:30 AM	3
				10:30 AM	11:00 AM	3
				11:00 AM	11:30 AM	2
				11:30 AM	12:00 PM	2

<tbody>
</tbody>

</i3))

 

[iggy_]

The reason that SUMPRODUCT would not work as is in this instance is that excel does not realize that the morning hours, should still fall within the appropriate range (i.e. Hour(9PM) = 21, Hour(1AM) = 1). You can try to incorporate a date in with the time that way the values are going in a continuous order or have some hidden rows to do such a calculation.

 

[markmzz]

Hi!

Maybe the formula below can helps:

In G4 and copy down

=SUMPRODUCT(--(B$4:B$15<=E4),--(F4<=B$4:B$15+MOD(C$4:C$15-B$4:B$15,1)))

	A	B	C	D	E	F	G	H
1
2
3	Artist	Time in	Time Out		Time in Duration		Artists
4	Sam Smith	21:00	06:00		21:00	21:30	2
5	Justin Bieber	21:00	06:00		21:30	22:00	3
6	Selena Gomez	21:30	06:30		22:00	22:30	4
7	Britney Spears	22:00	07:00		22:30	23:00	4
8	Christina Aguilera	22:45	07:45		23:00	23:30	5
9	Elle King	23:45	08:45		23:30	24:00	5
10	Miley Cyrus	23:45	08:45		00:00	00:30	1
11	Angelina Jolie	00:00	09:00		00:30	01:00	1
12	Brad Pitt	01:00	10:00		01:00	01:30	2
13	Justin Timberlake	02:00	11:00		01:30	02:00	2
14	Pink	03:00	12:00		02:00	02:30	3
15	Amy Lee	03:00	12:00		02:30	03:00	3
16					03:00	03:30	5
17					03:30	04:00	5
18					04:00	04:30	5
19					04:30	05:00	5
20					05:00	05:30	5
21					05:30	06:00	5
***	****************	********	*********	**	****************	******	*******	**

<tbody>
</tbody>

Markmzz

 

[Newbie987654321]

Hi Mark, I appreciate your help on this. It's almost there however the 23:45 should be counted within the 22:30-23:00 time range. And so far, the numbers are different from the table 2 results I was looking for. By the way, the columns E and F is the range for Time In.
So basically, from 9PM to 3AM, the number of artists should gradually increase, then by 6AM onwards it goes down as some of them times out.

 

[markmzz]

Hi Mark, I appreciate your help on this. It's almost there however the 23:45 should be counted within the 22:30-23:00 time range. And so far, the numbers are different from the table 2 results I was looking for. By the way, the columns E and F is the range for Time In.
So basically, from 9PM to 3AM, the number of artists should gradually increase, then by 6AM onwards it goes down as some of them times out.

Hi!

I'm sorry but I didn't understand what you want.

Could you explain better? With more examples.

Why 23:45 shoud be counted for the range 22:30 to 23:00?

Markmzz

 

[Newbie987654321]

Hi!

I'm sorry but I didn't understand what you want.

Could you explain better? With more examples.

Why 23:45 shoud be counted for the range 22:30 to 23:00?

Markmzz

Hi. Sorry for thr confusion, i meant 22:45 should be counted on a 2230-2300 time in range. With that, I mean Time In Duration 2230-2300 should be 5.
Then by 3am all artists should be there since the last artist comes in at 3am. So 0300-330 should be 12 until 6am.
The around 0600-0630, since 2 artists times out it should then decrease to 10. and so forth.

 

[markmzz]

Hi. Sorry for thr confusion, i meant 22:45 should be counted on a 2230-2300 time in range. With that, I mean Time In Duration 2230-2300 should be 5.
Then by 3am all artists should be there since the last artist comes in at 3am. So 0300-330 should be 12 until 6am.
The around 0600-0630, since 2 artists times out it should then decrease to 10. and so forth.

Hi!

I'm sorry, but I don't know how do that until now.

I think that Iggy_ (post #2) is right. We need to incorporate a date.

Anyway, lets wait for another user.

Ps: If I have another suggestion, I'll post here again.

Markmzz

 

[markmzz]

Hi!

Anyway, here is my formula with Iggy_ (post #2) sugestion:

	A	B	C	D	E	F	G	H
1
2
3	Artist	Time in	Time Out		Time in Duration		Artists
4	Sam Smith	21:00:00	06:00:00		21:00:00	21:30:00	2
5	Justin Bieber	21:00:00	06:00:00		21:30:00	22:00:00	3
6	Selena Gomez	21:30:00	06:30:00		22:00:00	22:30:00	4
7	Britney Spears	22:00:00	07:00:00		22:30:00	23:00:00	4
8	Christina Aguilera	22:45:00	07:45:00		23:00:00	23:30:00	5
9	Elle King	23:45:00	08:45:00		23:30:00	00:00:00	5
10	Miley Cyrus	23:45:00	08:45:00		00:00:00	00:30:00	8
11	Angelina Jolie	00:00:00	09:00:00		00:30:00	01:00:00	8
12	Brad Pitt	01:00:00	10:00:00		01:00:00	01:30:00	9
13	Justin Timberlake	02:00:00	11:00:00		01:30:00	02:00:00	9
14	Pink	03:00:00	12:00:00		02:00:00	02:30:00	10
15	Amy Lee	03:00:00	12:00:00		02:30:00	03:00:00	10
16					03:00:00	03:30:00	12
17					03:30:00	04:00:00	12
18					04:00:00	04:30:00	12
19					04:30:00	05:00:00	12
20					05:00:00	05:30:00	12
21					05:30:00	06:00:00	12
22					06:00:00	06:30:00	10
23					06:30:00	07:00:00	9
24					07:00:00	07:30:00	8
25					07:30:00	08:00:00	7
26					08:00:00	08:30:00	7
27					08:30:00	09:00:00	5
28					09:00:00	09:30:00	4
29					09:30:00	10:00:00	4
30					10:00:00	10:30:00	3
31					10:30:00	11:00:00	3
32					11:00:00	11:30:00	2
33					11:30:00	12:00:00	2
34
***	****************	********	*********	**	****************	******	*******	**

<tbody>
</tbody>

Markmzz