Calculating Sum of hours horizontally, based on incremental date columns

[benobee]

Hi there

Not sure how to approach this, but the problem is as the title describes.

I have columns that correspond to incremental weeks. On each row is a person's name.

I want to calculate the sum of the actual number of hours worked by a person, depending on the week.

The week is stipulated in a separate cell on a separate sheet for reference (let's say in Summary!$A$1).

So as an example, the sheet looks like this:

Name---Actual Hours To Date---1 June---8 June---15 June---22 June
David----------[ ??? ]------------- 40 ----- 20 ------ 10 ------- 0 ---

So if Summary!$A$1 = 8th June, then the Actual Hours To Date will be 60.

If Summary!$A$1 = 15th June, then the Actual Hours should read 70.

Any help would be appreciated.

Many thanks

Ben

 

[jorismoerings]

Hi,

Try this:

Book1
	A	B	C	D	E	F
1	Name	Actual Hours To Date	1-6-2018	8-6-2018	15-6-2018	22-6-2018
2	David	70	40	20	10	0
Sheet1

Cell Formulas
Range	Formula
B2	=SUM(OFFSET(C2,0,0,1,MATCH(summary!A1,Sheet1!C1:F1,0)))

 

[benobee]

Hi jorismoerings

Thanks for your input. This looks like it's exactly what I need! Much appreciated.

Kind Regards

Ben

 

[benobee]

And two further questions, if I may:

1. How would I calculate the reverse (i.e. all FUTURE hours from the specified date)?
2. how would the formula change if I wanted to calculate the total number of hours across the entire row, irrespective of date? (let's assume the columns extend all the way to Column CY)

 

[jorismoerings]

Hi,

Another approach same result with Summary $A$1 being 8-6-2018:

Book1
	A	B	C	D	E	F	G	H
1	Name	Actual Hours To Date	Future Hours	Total Hours	1-6-2018	8-6-2018	15-6-2018	22-6-2018
2	David	60	10	70	40	20	10	0
Sheet1

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(($E$1:$CY$1<=summary!$A$1)*($E$2:$CY$2))
C2	=SUMPRODUCT(($E$1:$CY$1>summary!$A$1)*($E2:$CY2))
D2	=SUM(E2:CY2)