Adding similar times from same dates

[jackfox68]

I have a spreadsheet that contains up to 13 accounts, Each account has multiple dates, and under each date there can be up to 24 hours, each listing a varying headcount number. I need to be able to total up the headcount number for each hour of the day for each date listed. An example of the layout is posted below. The output will go into another sheet with the dates listed across the top and the times down the side.

Excel 2010
	A	B	C	D	E	F	G
1	Planned Staffing Numbers by Interval
2
3	From:				04/20/2013 12:00 AM
4	To:				04/20/2013 11:59 PM
5
6	Organization: ATT SMB Sales Agents (Filtered by "All Scheduled")
7	Apr 20, 2013	Total	# On Contact	# Not On Contact	# Working	# Not Working	# Paid
8	7:30:00 AM	1.00	1.00	0.00	1.00	0.00	1.00
9	8:00:00 AM	4.00	4.00	0.00	4.00	0.00	4.00
10	8:30:00 AM	4.00	4.00	0.00	4.00	0.00	4.00
11	9:00:00 AM	4.00	3.50	0.50	3.50	0.50	4.00
12	9:30:00 AM	4.00	3.00	1.00	3.00	1.00	4.00
13	10:00:00 AM	4.00	3.50	0.50	3.50	0.50	4.00
14	10:30:00 AM	4.00	4.00	0.00	4.00	0.00	4.00
15	11:00:00 AM	4.00	4.00	0.00	4.00	0.00	4.00
16	11:30:00 AM	4.00	3.50	0.50	3.50	0.50	3.50
17	12:00:00 PM	4.00	2.00	2.00	2.00	2.00	2.00
18	12:30:00 PM	4.00	2.50	1.50	2.50	1.50	2.50
19	1:00:00 PM	4.00	4.00	0.00	4.00	0.00	4.00
20	1:30:00 PM	4.00	4.00	0.00	4.00	0.00	4.00
21	2:00:00 PM	5.00	5.00	0.00	5.00	0.00	5.00
22	2:30:00 PM	8.00	6.50	1.50	6.50	1.50	8.00
23	3:00:00 PM	8.00	7.50	0.50	7.50	0.50	8.00
24	3:30:00 PM	8.00	7.50	0.50	7.50	0.50	8.00
25	4:00:00 PM	7.00	5.50	1.50	5.50	1.50	7.00
26	4:30:00 PM	4.00	4.00	0.00	4.00	0.00	4.00
27	5:00:00 PM	4.00	4.00	0.00	4.00	0.00	4.00
28	5:30:00 PM	4.00	4.00	0.00	4.00	0.00	4.00
29	6:00:00 PM	4.00	3.00	1.00	3.00	1.00	3.00
30	6:30:00 PM	4.00	3.50	0.50	3.50	0.50	3.50
31	7:00:00 PM	4.00	3.00	1.00	3.00	1.00	3.00
32	7:30:00 PM	4.00	3.00	1.00	3.00	1.00	3.00
33	8:00:00 PM	4.00	3.50	0.50	3.50	0.50	3.50
34	8:30:00 PM	4.00	3.50	0.50	3.50	0.50	4.00
35	9:00:00 PM	4.00	3.00	1.00	3.00	1.00	4.00
36	9:30:00 PM	4.00	3.50	0.50	3.50	0.50	4.00
37	10:00:00 PM	4.00	4.00	0.00	4.00	0.00	4.00
38	10:30:00 PM	3.00	3.00	0.00	3.00	0.00	3.00
39	Organization: Avast Sales Agents (Filtered by "All Scheduled")
40	Apr 20, 2013	Total	# On Contact	# Not On Contact	# Working	# Not Working	# Paid
41	8:00:00 AM	1.00	1.00	0.00	1.00	0.00	1.00
Page1_1

Thanks

 

[konew1]

Do you mean you want a summary for all the 13 departments totalled together? Does the sheet list all 13 departments for one day, or is there data for previous days on the same sheet?

 

[jackfox68]

I want a summary for all 13 departments, for each 1/2 hour of the day, for each date listed. The report that is produced run horizontal and the dates are not necessarily in order so I am not even sure there is an excel formula that will be able to do this.

 

[Robert Mika]

I want a summary for all 13 departments, for each 1/2 hour of the day, for each date listed. The report that is produced run horizontal and the dates are not necessarily in order so I am not even sure there is an excel formula that will be able to do this.

Could you provide example of the outcome?

 

[jackfox68]

Could you provide example of the outcome?

It would look something like this.

Excel 2010
	O	P	Q	R	S
2	Time	Apr 20, 2013	Apr 21, 2013	Apr 22, 2013	Apr 23, 2013
3	12:00:00 AM	67.5	71.5	69.5	73.1
4	12:30:00 AM	52	56	54	57.6
5	1:00:00 AM	47	51	49	52.6
6	1:30:00 AM	36	40	38	41.6
7	2:00:00 AM	25	29	27	30.6
8	2:30:00 AM	16	20	18	21.6
9	3:00:00 AM	16	20	18	21.6
10	3:30:00 AM	16	20	18	21.6
11	4:00:00 AM	16	20	18	21.6
12	4:30:00 AM	16	20	18	21.6
13	5:00:00 AM	16	20	18	21.6
14	5:30:00 AM	16	20	18	21.6
15	6:00:00 AM	16	20	18	21.6
16	6:30:00 AM	16	20	18	21.6
17	7:00:00 AM	44	48	46	49.6
18	7:30:00 AM	51	55	53	56.6
19	8:00:00 AM	86	90	88	91.6
20	8:30:00 AM	89	93	91	94.6
21	9:00:00 AM	111.5	115.5	113.5	117.1
22	9:30:00 AM	118	122	120	123.6
23	10:00:00 AM	140	144	142	145.6
24	10:30:00 AM	145.5	149.5	147.5	151.1
25	11:00:00 AM	162.5	166.5	164.5	168.1
26	11:30:00 AM	172.5	176.5	174.5	178.1
27	12:00:00 PM	189.5	193.5	191.5	195.1
28	12:30:00 PM	197.5	201.5	199.5	203.1
29	1:00:00 PM	209	213	211	214.6
30	1:30:00 PM	220.5	224.5	222.5	226.1
31	2:00:00 PM	231	235	233	236.6
32	2:30:00 PM	238	242	240	243.6
33	3:00:00 PM	285	289	287	290.6
34	3:30:00 PM	264	268	266	269.6
35	4:00:00 PM	266	270	268	271.6
36	4:30:00 PM	235.5	239.5	237.5	241.1
37	5:00:00 PM	235	239	237	240.6
38	5:30:00 PM	236.5	240.5	238.5	242.1
39	6:00:00 PM	227.5	231.5	229.5	233.1
40	6:30:00 PM	216	220	218	221.6
41	7:00:00 PM	200.5	204.5	202.5	206.1
42	7:30:00 PM	186.5	190.5	188.5	192.1
43	8:00:00 PM	182.5	186.5	184.5	188.1
44	8:30:00 PM	164.5	168.5	166.5	170.1
45	9:00:00 PM	151.5	155.5	153.5	157.1
46	9:30:00 PM	144	148	146	149.6
47	10:00:00 PM	128.5	132.5	130.5	134.1
48	10:30:00 PM	120	124	122	125.6
49	11:00:00 PM	109	113	111	114.6
50	11:30:00 PM	91	95	93	96.6
Page1_1

 

[Robert Mika]

It would look something like this.

Excel 2010

	O	P	Q	R	S
2	Time	Apr 20, 2013	Apr 21, 2013	Apr 22, 2013	Apr 23, 2013
3	12:00:00 AM	67.5	71.5	69.5	73.1
4	12:30:00 AM	52	56	54	57.6
5	1:00:00 AM	47	51	49	52.6
6	1:30:00 AM	36	40	38	41.6
7	2:00:00 AM	25	29	27	30.6
8	2:30:00 AM	16	20	18	21.6
9	3:00:00 AM	16	20	18	21.6
10	3:30:00 AM	16	20	18	21.6
11	4:00:00 AM	16	20	18	21.6
12	4:30:00 AM	16	20	18	21.6
13	5:00:00 AM	16	20	18	21.6
14	5:30:00 AM	16	20	18	21.6
15	6:00:00 AM	16	20	18	21.6
16	6:30:00 AM	16	20	18	21.6
17	7:00:00 AM	44	48	46	49.6
18	7:30:00 AM	51	55	53	56.6
19	8:00:00 AM	86	90	88	91.6
20	8:30:00 AM	89	93	91	94.6
21	9:00:00 AM	111.5	115.5	113.5	117.1
22	9:30:00 AM	118	122	120	123.6
23	10:00:00 AM	140	144	142	145.6
24	10:30:00 AM	145.5	149.5	147.5	151.1
25	11:00:00 AM	162.5	166.5	164.5	168.1
26	11:30:00 AM	172.5	176.5	174.5	178.1
27	12:00:00 PM	189.5	193.5	191.5	195.1
28	12:30:00 PM	197.5	201.5	199.5	203.1
29	1:00:00 PM	209	213	211	214.6
30	1:30:00 PM	220.5	224.5	222.5	226.1
31	2:00:00 PM	231	235	233	236.6
32	2:30:00 PM	238	242	240	243.6
33	3:00:00 PM	285	289	287	290.6
34	3:30:00 PM	264	268	266	269.6
35	4:00:00 PM	266	270	268	271.6
36	4:30:00 PM	235.5	239.5	237.5	241.1
37	5:00:00 PM	235	239	237	240.6
38	5:30:00 PM	236.5	240.5	238.5	242.1
39	6:00:00 PM	227.5	231.5	229.5	233.1
40	6:30:00 PM	216	220	218	221.6
41	7:00:00 PM	200.5	204.5	202.5	206.1
42	7:30:00 PM	186.5	190.5	188.5	192.1
43	8:00:00 PM	182.5	186.5	184.5	188.1
44	8:30:00 PM	164.5	168.5	166.5	170.1
45	9:00:00 PM	151.5	155.5	153.5	157.1
46	9:30:00 PM	144	148	146	149.6
47	10:00:00 PM	128.5	132.5	130.5	134.1
48	10:30:00 PM	120	124	122	125.6
49	11:00:00 PM	109	113	111	114.6
50	11:30:00 PM	91	95	93	96.6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Page1_1​

Is this total from row 8 to 38 or your data are going beyond that?
(as I see you have anoth 20 April in row 41)

 

[jackfox68]

The rows continue on and the dates also continue on as well. I need to know how many people I have working at each 30 minute interval for each day of the we across all departments combined. Does that make sense?

 

[jackfox68]

OK - I think I am doing a bad job of explaining the problem. In Column A there is a list of dates. The same date may appear more than one time in column A. Under each date there will be a department. Each department will only appear one time under each date. (so for 4/21 department A shows up, then 4/22 it show up again) Underneath the Date\Department heading will be 24 hours of 30 minute intervals. I need to total up each interval under each relative date(regardless of the department). So for the date 4/21 I need to total up the everything attached to the 8:00 AM interval, then the 8:30 AM interval and so on. Am I making sense yet? Thanks!

 

[konew1]

OK - I think I am doing a bad job of explaining the problem. In Column A there is a list of dates. The same date may appear more than one time in column A. Under each date there will be a department. Each department will only appear one time under each date. (so for 4/21 department A shows up, then 4/22 it show up again) Underneath the Date\Department heading will be 24 hours of 30 minute intervals. I need to total up each interval under each relative date(regardless of the department). So for the date 4/21 I need to total up the everything attached to the 8:00 AM interval, then the 8:30 AM interval and so on. Am I making sense yet? Thanks!

Does your data include every half hour interval of the day? Your first posting shows the first department starting at 7:30am and the next department on row 41 starting at 8:00am.

 

[konew1]

If your data is sorted with the earliest dates at the top, then this should do it. You need a helper row in your summary, I used J2:K2
The formula in them is an array formula to be entered with Ctrl+Shift+Enter, not just Enter. When you do that Excel will put {} around the formula (don't type the {} yourself.

Excel Workbook
	J	K
2	76	1048576
3	*	*
4	*	*
5	*	*
6	*	*
7	Apr 20, 2013	Apr 21, 2013
8	2	0
9	9	1
Sheet1

Test the formula on your data. I extended your data for testing, so you will not recognise the results presented