sum hours worked in concurrent 7 day periods for individuals

Hi there,

help again!

I have a resource programme which details staff members and their daily hours worked.

I want to make a summary table that returns the sum of the "total actual working hours" for the week for each individual (in the example, Person 1, Person 2 etc.)

I know i can manually autosum this but i want to be able to include a formula that i can drag across and down.

I have hopefully provided an attachment which details what i'm trying to achieve

Any help, gratefully received

Administrative Note:

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings. 'Northeast' doesn't tell us where in the world you are (I presume England, but this is a global forum).

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.

Firstly, you need to get rid of ALL merged cells.

EDIT This might work for you:

=SUMPRODUCT(($C$2:$C$19=$C28)*($E$1:$Y$1>=D$27)*($E$1:$Y$1<(D$27+7)),$E$2:$Y$19)

You’re welcome!