Waiting Room at a given time

[mduntley]

Hello. I am trying to figure this out. I work at a hospital, and I want to know at any given time, how many people are in the waiting room. I have got all the statistic that will be plug in. It will be more then one day. As I change the date and time, so will the number of people waiting. In this example. At 18:30, 4 people are waiting, but you can see 2 people after the first one came in and was helped . I was thinking of COUNTIF function, but I am not sure.

This space are patient name	01/04/15	14:44	14:50	date	01/04/15
	01/04/15	14:48	14:56	time	18:30
	01/04/15	15:39	15:50	how many people are waitng
	01/04/15	16:25	19:34
	01/04/15	17:51	17:59
	01/04/15	17:57	18:06
	01/04/15	18:18	18:43
	01/04/15	18:19	18:43
	01/04/15	18:21	18:52
	01/04/15	20:02	20:13
	01/04/15	20:09	20:30
	01/04/15	20:18	20:21
	01/04/15	21:21	21:27
	01/04/15	21:37	21:38
	01/04/15	22:02	22:09

<tbody>
</tbody>

Thanks for your help

 

[gaz_chops]

Welcome to the forum.

Assuming your data starts in A1, G1=Date, G2=Time

=COUNTIFS(C1:C15,"<"&G2,D1:D15,">"&G2,B1:B15,G1)

 

[mduntley]

Thank you so much, I was trying to figure it out, and i new the right one was COUNTIFS. Thank you so much

 

[gaz_chops]

You're welcome.

 

[mduntley]

Just want to ask if this is possible. I want to see if there is a formula that I can add that will only include a person who waited more then 30 minutes. In this example, Person 1 will show up in the section for 7:00 and 7:30 because he waited more then 30 minutes

Original formula
=COUNTIFS(C:C,"<"&H17,D:D,">"&H17,B:B,G5)

I tried to fix the formula to what I think would work, but it didn't

=COUNTIFS(C:C,"<"&H17,D:D,">"&(H17+TIME(0,30,)),B:B,G17)

account	time	wait	room		Week	Date	Time	how many people
	12/28/2014	0:52	0:53		Week of 12/28 - 1/3	12/28/2014	0:00	0
	12/28/2014	2:59	3:00		Week of 12/28 - 1/3	12/28/2014	0:30	0
person 1	12/28/2014	6:16	7:34		Week of 12/28 - 1/3	12/28/2014	1:00	0
person 2	12/28/2014	7:20	7:34		Week of 12/28 - 1/3	12/28/2014	1:30	0
	12/28/2014	8:07	8:18		Week of 12/28 - 1/3	12/28/2014	2:00	0
	12/28/2014	9:12	9:24		Week of 12/28 - 1/3	12/28/2014	2:30	0
	12/28/2014	9:26	9:36		Week of 12/28 - 1/3	12/28/2014	3:00	0
	12/28/2014	9:38	9:45		Week of 12/28 - 1/3	12/28/2014	3:30	0
	12/28/2014	9:48	10:03		Week of 12/28 - 1/3	12/28/2014	4:00	0
	12/28/2014	10:23	10:32		Week of 12/28 - 1/3	12/28/2014	4:30	0
	12/28/2014	11:01	11:11		Week of 12/28 - 1/3	12/28/2014	5:00	0
	12/28/2014	11:53	12:00		Week of 12/28 - 1/3	12/28/2014	5:30	0
	12/28/2014	12:02	12:15		Week of 12/28 - 1/3	12/28/2014	6:00	0
	12/28/2014	12:44	12:50		Week of 12/28 - 1/3	12/28/2014	6:30	1
	12/28/2014	13:00	13:06		Week of 12/28 - 1/3	12/28/2014	7:00	1
	12/28/2014	14:12	14:18		Week of 12/28 - 1/3	12/28/2014	7:30	0
	12/28/2014	14:24	14:32		Week of 12/28 - 1/3	12/28/2014	8:00	0

<tbody>
</tbody>

 

[mduntley]

I couldn't find a edit button. But I want to further explain what I'm looking for. In the original formula patient 1 will show in the category of 6:30, 7:00, and 7:30. I want to show him in the 7:00 and 7:30 because and not in 6:30 because he only waited 14 minute, thus he wouldn't be in the 6:30 section

 

[gaz_chops]

Test this one

=COUNTIFS($B$2:$B$1000,G2,$C$2:$C$1000,"<="&(H2-TIME(,30,)),$D$2:$D$1000,">="&H2)