Forulma Needed

[PR]

It works,
Many Thanks
Paul R

Ok. Do this first:
Put headings in as follows A1 - Name , B1 - Start (Date), B2 - End (Date)

Next, using your data p1-p5 in cells A2 to C6. Define B2:B6 with the name
START, and define cells C2:C6 with the name END.

Then follow the previous instructions from the line
So instead, use this solution:

This should work.

Regards
Paul Allen

 

[PR]

Paul,
I have done that and it works great.

I would like to add one more thing to this if possible, how can I filter on
departments, so that the numbers change so that it reflects just the
department I have chosen.

Paul

 

[Paul Allen]

OK. Still using the original sheet, do the following
In cell D1 put the text Department. Define cells D26 with the name DEPT
Put the Department names in these cells.

Now go further down the sheet, where the main table is. There is a cell with
the word WEEKLY in it. On my sheet this is in cell A14. In Cell A13 put the
name of the department that you wish to summarise. Eg Department A.

Next go to the main body of the table, the first cell to the right of the
first date. This is cell B15 in my sheet.

Change the formula in this cell. Change it to the following:
=SUMPRODUCT((START<=$A15+B$13)*(END>$A15+B$13)*(DEPT=$A$13))

Copy right to cell H15 and copy down as far as necessary.

Now, every time you wish to see a different department, change the value in
A13 to the department that you want to see.

Regards
Paul Allen