Date Ranges and IF Statements

[wizpeter]

Hi,

I've been banging my head against a wall with this and I'm reaching out to the internet for some wisdom and help.

I have a basic spreadsheet that shows a request for the cover of hours which is then either then picked up by Group 1, Group 2 or Group 3.

What I need to do is show in the report a summary of the requests for hours cover in each four weekly period along with the hours that are/were outstanding. I originally tried a pivot table which I can do for a single date range but I understand it isn't possible to show multiple date ranges in one pivot table, the use of multiple pivot tables isn't practical as I have a number of areas that I need to duplicate the below for.

I've tried COUNTIFS and IF statements referencing the date periods in column A and B but I just can't get it to work.

Glad to be here and looking to learn.

Report

1
2	Start	End	Cover	Hours	Cover	Hours	Cover	Hours	Cover	Hours
3	05/04/15	02/05/15	COUNT of Group 1 in date range	SUM of Group 1 hours in date range					COUNT of cover not assigned to any Group in date range	SUM of hours not assigned to any Group hours in date range
4	03/05/15	30/05/15
5	31/05/15	27/06/15
6	28/06/15	25/07/15
7	26/07/15	22/08/15
8	23/08/15	19/09/15
9	20/09/15	17/10/15
10	18/10/15	14/11/15
11	15/11/15	12/12/15
12	13/12/15	09/01/16
13	10/01/16	06/02/16
14	07/02/16	05/03/16
15	06/03/16	02/04/16

<colgroup><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="111"><col width="114"></colgroup> <tbody>
[TD="width: 86"][/TD]
[TD="width: 86"]A[/TD]
[TD="width: 86"]B[/TD]
[TD="width: 86"]C[/TD]
[TD="width: 86"]D[/TD]
[TD="width: 86"]E[/TD]
[TD="width: 86"]F[/TD]
[TD="width: 86"]G[/TD]
[TD="width: 86"]H[/TD]
[TD="width: 111"]I[/TD]
[TD="width: 114"]J[/TD]

[TD="colspan: 2"] Period [/TD]
[TD="colspan: 2"] Group 1 [/TD]
[TD="colspan: 2"] Group 2 [/TD]
[TD="colspan: 2"] Group 3 [/TD]
[TD="colspan: 2"] Outstanding [/TD]

</tbody>

Data

1	Date Request Received	Staff Member Requesting	Unique Request ID	Weekly Hours Cover Required	Group Covering	Cover Provided By
2	e.g. 05/04/2015	e.g RH	e.g 123456	e.g 10.5	e.g Group 1	e.g Unit 1

<colgroup><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"></colgroup> <tbody>
[TD="width: 86"][/TD]
[TD="width: 86"]A[/TD]
[TD="width: 86"]B[/TD]
[TD="width: 86"]C[/TD]
[TD="width: 86"]D[/TD]
[TD="width: 86"]E[/TD]
[TD="width: 86"]F[/TD]

</tbody>