sum number of certain period which falls in specified Week number / Month

[annalee]

Hello All

Below is the Daily summary which I had computed using Excel 2007.

[TABLE="width: 1259"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[TD]Column E
[/TD]
[TD]Column F
[/TD]
[TD]Column G
[/TD]
[TD]Column H
[/TD]
[TD]Column I
[/TD]
[TD]Column J
[/TD]
[TD]Column K
[/TD]
[TD]Column L
[/TD]
[TD]Column M
[/TD]
[TD]Column N
[/TD]
[/TR]
[TR]
[TD]Row 2
[/TD]
[TD]Department
[/TD]
[TD]No. of days to complete project
[/TD]
[TD]Duration (Days) given
[/TD]
[TD]Staff needed per dept
[/TD]
[TD]Start Date
[/TD]
[TD]End Date
[/TD]
[TD]28/01/15
[/TD]
[TD]29/01/15
[/TD]
[TD]30/01/15
[/TD]
[TD]31/01/15
[/TD]
[TD]01/02/15
[/TD]
[TD]02/02/15
[/TD]
[TD]03/02/15
[/TD]
[/TR]
[TR]
[TD]Row 3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Total headcount needed
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Row 4
[/TD]
[TD]Training
[/TD]
[TD]2
[/TD]
[TD]6
[/TD]
[TD]1
[/TD]
[TD]28-Jan-15
[/TD]
[TD]3-Feb-15
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Row 5
[/TD]
[TD]HRM
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]29-Jan-15
[/TD]
[TD]3-Feb-15
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Row 6
[/TD]
[TD]Finance
[/TD]
[TD]17
[/TD]
[TD]2
[/TD]
[TD]9
[/TD]
[TD]28-Jan-15
[/TD]
[TD]29-Jan-15
[/TD]
[TD]9
[/TD]
[TD]9
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]Row 7
[/TD]
[TD]Purchasing
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]28-Jan-15
[/TD]
[TD]1-Feb-15
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]Row 8
[/TD]
[TD]Mechanical
[/TD]
[TD]1
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]28-Jan-15
[/TD]
[TD]2-Feb-15
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]


From cell H4:N8, cells will auto compute:
- If date in cell H2 doesn't fall on sunday, return 1 if date in cell H2 is within F4 to G4 AND multiply by number of staff required within the period
NOTE: (The lesser duration given in D4 and more days taken to complete project in C4, the more staff is required)

Function to be pasted in F4 is = IF(TEXT(H$2, "dddd")="SUNDAY", 0,IF(AND(H$2>=$F4,H$2<=$G4),1,0)*$E4) ---------- Copy paste across and downwards

- if date in cell F2 fall on Sunday, return 0 ---------- Copy paste downwards

Function to be pasted in E4 is =ROUNDUP($C4/$D4,0)

- Cell H3:N3 are to calculate total workers overlapped.

Function to be pasted in H3 is =SUM(H4:H9922) ---------- Copy paste across

QUESTION 1 (New worksheet):

I would like to summarise the daily summary into weekly summary by week number per department in a new worksheet

E.g: 28 Jan to 31 Jan is week 5 & 1 Feb to 3 Feb is week 6

How do I take the highest number within week 5 & week 6 respectively and reflect the number in Cell C4 below?

[TABLE="width: 407"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[/TR]
[TR]
[TD]Row 2
[/TD]
[TD]Week No.
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]Row 3
[/TD]
[TD]Breakdown - Department
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Row 4
[/TD]
[TD]Training
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Row 5
[/TD]
[TD]HRM
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Row 6
[/TD]
[TD]Finance
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Row 7
[/TD]
[TD]Purchasing
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Row 8
[/TD]
[TD]Mechanical
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

QUESTION 2 (Another new worksheet):
I would like to summarise the daily summary into monthly summary by the month per department in a new worksheet

E.g: 28 Jan to 31 Jan is January & 1 Feb to 3 Feb is February

How do I take the highest number within month of January & February respectively and reflect the number in Cell C4 below?

[TABLE="width: 407"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[/TR]
[TR]
[TD]Row 2
[/TD]
[TD]Month
[/TD]
[TD]Jan
[/TD]
[TD]Feb
[/TD]
[/TR]
[TR]
[TD]Row 3
[/TD]
[TD]Breakdown - Department
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Row 4
[/TD]
[TD]Training
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Row 5
[/TD]
[TD]HRM
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Row 6
[/TD]
[TD]Finance
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Row 7
[/TD]
[TD]Purchasing
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Row 8
[/TD]
[TD]Mechanical
[/TD]
[TD]
[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Please let me know if clarifications are required. Thanks!

 

[annalee]

Pingz~