Pls help for Simple Calculation

[Deepak Sharma]

Hi Mike,

I have Sheet1 which has the data as below

The Sheet1 will have the data as follows and it differs for every day

- So for 19th Oct the task that were completed started at 06:00 AM and on
20th Oct started at 00:45 and this way the number of task started and
completed will differ

-----------------------------------------------------------
for 19th October the Task completed - will be as follows in Sheet1
-----------------------------------------------------------
A B
06:00 3
07:30 1
08:00 1
08:15 1
08:45 1
09:00 1
09:15 3
09:30 2
09:45 3
10:00 4
10:15 2
10:30 1
10:45 2
11:00 1
11:15 4
11:45 1
12:00 2
12:15 4
12:30 0
12:45 1
13:00 1
13:15 1
13:30 2
13:45 4
14:00 0
14:15 2
14:45 1
15:00 1
15:15 1
16:00 1
16:15 1
16:30 2
16:45 1
17:00 1
17:30 1
17:45 1
18:15 1
18:30 0
20:15 1
---------------------------------------
For 20th Oct - will be as follows in Sheet2
---------------------------------------
A B
00:45 1
03:00 1
07:30 2
08:00 1
08:15 2
08:30 3
08:45 2
09:00 2
09:15 3
09:30 3
09:45 2
10:00 3
10:30 2
10:45 3
11:00 1
11:15 2
11:30 2
12:00 2
12:15 2
12:30 1
12:45 1
13:15 2
14:15 3
14:30 5
14:45 1
15:00 1
15:30 1
15:45 1
16:00 2
16:15 1
---------------------------------------
For 21st Oct - will be as follows in Sheet3
---------------------------------------
A B
07:30 1
08:00 2
08:15 0
08:45 2
09:00 1
09:15 5
09:30 0
09:45 2
10:00 1
10:15 3
10:30 4
10:45 1
11:00 3
11:15 2
11:30 1
11:45 2
12:15 1
13:00 2
14:00 1
14:15 1
14:45 2
15:15 2
15:45 2
16:00 1
17:00 1
17:15 1
18:00 1
21:00 1
21:15 1
21:30 0
21:45 1



*******************************************************
The Result on sheet4 named "Result" will be as follows
*******************************************************

A B C D
E
19-Oct 20-Oct 21-Oct 22-Oct
00:00-01:00
01:00-02:00
02:00-03:00
03:00-04:00
04:00-05:00
05:00-06:00
06:00-07:00
07:00-08:00
08:00-09:00
09:00-10:00
10:00-11:00
11:00-12:00
12:00-13:00
13:00-14:00
14:00-15:00
15:00-16:00
16:00-17:00
17:00-18:00
18:00-19:00
19:00-20:00
20:00-21:00
21:00-22:00
22:00-23:00
23:00-24:00
20:00-21:00
21:00-22:00
22:00-23:00
23:00-24:00

So B2 will have the total number of jobs jobs completed on 19th Oct between
00:00-01:00, 01:00-02:00 and so on

 

[JLatham]

Make certain that on your Summary sheet, the start time and end time are in
columns A and B. I believe they probably are because you list columns A-E,
but your entries show 00:00-01:00. The formulas below assume that the
entries on Sheet1, Sheet2 and Sheet3 start on row 2, as does the information
on the Summary sheet.
On the Summary sheet in C2, use this formula
=SUMPRODUCT(--(Sheet1!A$2:A$40>=A2),--(Sheet1!A$2:A$40<B2),--(Sheet1!B$2:B$40))
On the Summary sheet in D2, use this formula
=SUMPRODUCT(--(Sheet2!A$2:A$31>=A2),--(Sheet2!A$2:A$31<B2),--(Sheet2!B$2:B$31))
On the Summary sheet in E2, use this formula
=SUMPRODUCT(--(Sheet3!A$2:A$32>=A2),--(Sheet3!A$2:A$32<B2),--(Sheet3!B$2:B$32))

Then fill the formulas down to the end of the hours list in columns A and B
on the Summary sheet. Make sure that columns C, D and E are formatted as
General, and didn't get changed to display time formatted values.

If you have Excel 2007, use these formulas (and fill down also)
In C2
=SUMIFS(Sheet1!B:B,Sheet1!A:A,">=" & A2,Sheet1!A:A,"<" & B2)
in D2
=SUMIFS(Sheet2!B:B,Sheet2!A:A,">=" & A2,Sheet2!A:A,"<" & B2)
and in E2
=SUMIFS(Sheet3!B:B,Sheet3!A:A,">=" & A2,Sheet3!A:A,"<" & B2)