Counting Help

M

mstevens157

New Member
Joined
Dec 19, 2012
Messages
6
I have the following table. It shows by day and by operating room the ealiest time a procedure started and the latest time a procedure ended. I am trying to figure out how I can count the number of rooms thet there operating at any given time. Something like the second table (I just put the totals in as examples they are not right). Any help would be appreciated.

Excel 2007
A
B
C
D
1
Date
Room
Min
Max
2
11/1/2010
OR 1
8:00
19:30
3
11/1/2010
OR 2
8:37
16:55
4
11/1/2010
OR 3
6:55
14:20
5
11/1/2010
OR 4
7:57
18:03
6
11/1/2010
OR 5
7:58
18:03
7
11/1/2010
OR 6
8:11
16:07
8
11/1/2010
OR 7
10:12
16:33
9
11/1/2010
OR 8
7:06
17:07
10
11/2/2010
OR 1
8:33
12:59
11
11/2/2010
OR 2
8:30
13:42
12
11/2/2010
OR 3
6:55
11:35
13
11/2/2010
OR 4
6:41
14:10
14
11/2/2010
OR 5
9:42
12:46
15
11/2/2010
OR 6
7:13
13:12
16
11/2/2010
OR 7
7:45
12:06
17
11/2/2010
OR 8
7:15
12:25
18
11/3/2010
OR 1
8:55
17:53
19
11/3/2010
OR 2
7:02
15:56
20
11/3/2010
OR 3
6:50
15:06
21
11/3/2010
OR 4
7:12
12:10
22
11/3/2010
OR 5
8:24
15:05
23
11/3/2010
OR 6
9:58
19:09
24
11/3/2010
OR 7
7:04
15:40
25
11/3/2010
OR 8
8:08
14:56
26
11/4/2010
OR 1
9:35
14:04
27
11/4/2010
OR 2
7:03
15:26
28
11/4/2010
OR 3
8:32
17:24
29
11/4/2010
OR 4
7:56
14:12
30
11/4/2010
OR 5
10:11
14:04
31
11/4/2010
OR 6
8:55
14:10
32
11/4/2010
OR 7
12:11
12:49

<TBODY>
</TBODY>


Excel 2007
KLMN
20700 - 12001200-15001500-1700
311/1/2010885
411/2/2010538
511/3/2010555

<COLGROUP><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>
Combined​
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
mstevens157 said:
I have the following table. It shows by day and by operating room the ealiest time a procedure started and the latest time a procedure ended. I am trying to figure out how I can count the number of rooms thet there operating at any given time. Something like the second table (I just put the totals in as examples they are not right). Any help would be appreciated.



Excel 2007
K
L
M
N
2
0700 - 1200
1200-1500
1500-1700
3
11/1/2010
8
8
5
4
11/2/2010
5
3
8
5
11/3/2010
5
5
5

<TBODY>
</TBODY>
Combined​
Click to expand...
You need to refine your summay criteria as the times overlap. For example, if a procedure starts or ends at 1200 which column should that be included in? Should it be included in column L or column M?

You should define the time ranges something like this:

0700 to 1159
1200 to 1459
1500 to 1659

Or:

0701 to 1200
1201 to 1500
1501 to 1700

Also, they should be in separate cells.

Book1
KLMN
2_7:0012:0015:00
3_11:5914:5916:59
411/1/2012885
Sheet1
 
Upvote 0
Here is a weeks worth of data and what I would like the results to look like.

Excel 2007
ABCDE
1DateRoomMinMaxTotal
211/01/10OR 18:0019:3011.50
311/01/10OR 28:3716:558.30
411/01/10OR 36:5514:207.42
511/01/10OR 47:5718:0310.10
611/01/10OR 57:5818:0310.08
711/01/10OR 68:1116:077.93
811/01/10OR 710:1216:336.35
911/01/10OR 87:0617:0710.02
1011/02/10OR 18:3312:594.43
1111/02/10OR 28:3013:425.20
1211/02/10OR 36:5511:354.67
1311/02/10OR 46:4114:107.48
1411/02/10OR 59:4212:463.07
1511/02/10OR 67:1313:125.98
1611/02/10OR 77:4512:064.35
1711/02/10OR 87:1512:255.17
1811/03/10OR 18:5517:538.97
1911/03/10OR 27:0215:568.90
2011/03/10OR 36:5015:068.27
2111/03/10OR 47:1212:104.97
2211/03/10OR 58:2415:056.68
2311/03/10OR 69:5819:099.18
2411/03/10OR 77:0415:408.60
2511/03/10OR 88:0814:566.80
2611/04/10OR 19:3514:044.48
2711/04/10OR 27:0315:268.38
2811/04/10OR 38:3217:248.87
2911/04/10OR 47:5614:126.27
3011/04/10OR 510:1114:043.88
3111/04/10OR 68:5514:105.25
3211/04/10OR 712:1112:490.63
3311/04/10OR 88:2815:447.27
3411/05/10OR 16:4014:568.27
3511/05/10OR 27:2815:458.28
3611/05/10OR 38:2016:508.50
3711/05/10OR 47:0317:4910.77
3811/05/10OR 58:2713:084.68
3911/05/10OR 67:0212:175.25
4011/05/10OR 78:4312:293.77
4111/05/10OR 89:1915:386.32

<COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>
Sheet1​



Then the Results

Excel 2007
HIJKLMNOPQRS
266:308:009:0010:0011:0012:0013:0014:0015:0016:00
277:598:599:5910:5911:5912:5913:5914:5915:5916:59> 17:00
2811/1/201088888888774
2911/2/201057888731000
3011/3/201047888877622
3111/4/201025677877311
3211/5/201047888855421

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>
Sheet1​
 
Upvote 0
mstevens157 said:
Here is a weeks worth of data and what I would like the results to look like.

Excel 2007
A
B
C
D
E
1
Date
Room
Min
Max
Total
2
11/01/10
OR 1
8:00
19:30
11.50
3
11/01/10
OR 2
8:37
16:55
8.30
4
11/01/10
OR 3
6:55
14:20
7.42
5
11/01/10
OR 4
7:57
18:03
10.10
6
11/01/10
OR 5
7:58
18:03
10.08
7
11/01/10
OR 6
8:11
16:07
7.93
8
11/01/10
OR 7
10:12
16:33
6.35
9
11/01/10
OR 8
7:06
17:07
10.02
10
11/02/10
OR 1
8:33
12:59
4.43
11
11/02/10
OR 2
8:30
13:42
5.20
12
11/02/10
OR 3
6:55
11:35
4.67
13
11/02/10
OR 4
6:41
14:10
7.48
14
11/02/10
OR 5
9:42
12:46
3.07
15
11/02/10
OR 6
7:13
13:12
5.98
16
11/02/10
OR 7
7:45
12:06
4.35
17
11/02/10
OR 8
7:15
12:25
5.17
18
11/03/10
OR 1
8:55
17:53
8.97
19
11/03/10
OR 2
7:02
15:56
8.90
20
11/03/10
OR 3
6:50
15:06
8.27
21
11/03/10
OR 4
7:12
12:10
4.97
22
11/03/10
OR 5
8:24
15:05
6.68
23
11/03/10
OR 6
9:58
19:09
9.18
24
11/03/10
OR 7
7:04
15:40
8.60
25
11/03/10
OR 8
8:08
14:56
6.80
26
11/04/10
OR 1
9:35
14:04
4.48
27
11/04/10
OR 2
7:03
15:26
8.38
28
11/04/10
OR 3
8:32
17:24
8.87
29
11/04/10
OR 4
7:56
14:12
6.27
30
11/04/10
OR 5
10:11
14:04
3.88
31
11/04/10
OR 6
8:55
14:10
5.25
32
11/04/10
OR 7
12:11
12:49
0.63
33
11/04/10
OR 8
8:28
15:44
7.27
34
11/05/10
OR 1
6:40
14:56
8.27
35
11/05/10
OR 2
7:28
15:45
8.28
36
11/05/10
OR 3
8:20
16:50
8.50
37
11/05/10
OR 4
7:03
17:49
10.77
38
11/05/10
OR 5
8:27
13:08
4.68
39
11/05/10
OR 6
7:02
12:17
5.25
40
11/05/10
OR 7
8:43
12:29
3.77
41
11/05/10
OR 8
9:19
15:38
6.32

<TBODY>
</TBODY>
Sheet1​



Then the Results

Excel 2007
H
I
J
K
L
M
N
O
P
Q
R
S
26
6:30
8:00
9:00
10:00
11:00
12:00
13:00
14:00
15:00
16:00
27
7:59
8:59
9:59
10:59
11:59
12:59
13:59
14:59
15:59
16:59
> 17:00
28
11/1/2010
8
8
8
8
8
8
8
8
7
7
4
29
11/2/2010
5
7
8
8
8
7
3
1
30
11/3/2010
4
7
8
8
8
8
7
7
6
2
2
31
11/4/2010
2
5
6
7
7
8
7
7
3
1
1
32
11/5/2010
4
7
8
8
8
8
5
5
4
2
1

<TBODY>
</TBODY>
Sheet1​
Click to expand...
I don't see how you arrive at those results.

Explain how you get a count of 8 for 11/1/2010 and 6:30 - 7:59.
 
Upvote 0
mstevens157 said:
because i miss counted. Been staring at it so long it all runs rogether. It should be 4.
Click to expand...
I'm not sure if these are the expected results but here's what I came up with...

Book1
ABC
1DatesStartEnd
211/1/20108:0019:30
311/1/20108:3716:55
411/1/20106:5514:20
511/1/20107:5718:03
611/1/20107:5818:03
711/1/20108:1116:07
811/1/201010:1216:33
911/1/20107:0617:07
Sheet1

Book1
EFGH
1DateFromToCount
211/1/20106:307:594
3_8:008:597
4_9:009:597
5_10:0010:598
6_11:0011:598
7_12:0012:598
8_13:0013:598
9_14:0014:598
10_15:0015:597
11_16:0016:597
12_17:0017:004
Sheet1

This formula entered in H2 and copied down:

=SUMPRODUCT(--(Dates=E$2),SIGN((F2>=Start)*(F2<=End)+(G2>=Start)*(G2<=End)))
 
Upvote 0
Note how the last time range (From - To) is the same:

Book1
EFGH
1DateFromToCount
211/1/20106:307:594
3_8:008:597
4_9:009:597
5_10:0010:598
6_11:0011:598
7_12:0012:598
8_13:0013:598
9_14:0014:598
10_15:0015:597
11_16:0016:597
12_17:0017:004
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
vba at each change
2
Replies
15
Views
721
kevin9999
K
J
  • Solved
IFS
Replies
4
Views
152
Peter_SSs
Peter_SSs
S
  • Question
need ranking
Replies
5
Views
140
Cubist
Cubist
N
  • Question
Need Formula help
Replies
5
Views
396
AhoyNC
A
I
  • Question
Formula to count duplicate Values every x # of rows
Replies
8
Views
325
Fluff
Fluff

Forum statistics

Threads
1,216,033
Messages
6,128,427
Members
449,450
Latest member
gunars

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top