# Waiting Room at a given time

#### mduntley

##### Board Regular
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>

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Welcome to the forum.

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

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

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

You're welcome.

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>

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

Test this one

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

Last edited:

Replies
5
Views
542
Replies
6
Views
1K
Replies
1
Views
343
Replies
4
Views
524
Replies
1
Views
338

1,196,483
Messages
6,015,461
Members
441,898
Latest member
kofafa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back