Sum from Start Day

[markoakes]

I have the table below. I need a formula in column L to sum from the Start Day in column A. So for the first row it would sum from Day 5 through Day 10, the second row would be from Day 6 through Day 10 and so on.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	Start	Day 1	Day 2	Day 3	Day 4	Day 5	Day 6	Day 7	Day 8	Day 9	Day 10	Sum
2	Day 5	3.2	3.2	3.2	3.0	2.6	2.6	2.4	1.8	2.4	3.2	15.0
3	Day 6	1.7	1.5	1.6	1.8	1.7	1.4	2.0	2.6	1.7	1.6
4	Day 3	0.2	0.2	0.2	0.2	0.2	0.2	0.3	0.2	0.6	0.2
5	Day 8	3.2	3.2	4.2	4.8	4.8	6.2	6.4	6.0	6.4	4.2
6	Day 5	0.8	0.9	0.8	0.0	0.0	0.0	0.0	0.0	0.0	0.8
7	Day 1	0.3	0.3	0.5	0.4	0.3	0.5	0.6	1.2	1.0	0.5
8	Day 2	8.2	8.6	8.8	8.8	8.8	8.6	8.4	8.8	9.0	8.8
9	Day 2	0.0	1.0	0.9	0.9	0.9	2.3	3.6	4.9	4.3	0.9
10	Day 9	1.2	0.9	1.0	1.0	0.6	0.8	1.5	1.7	1.8	1.0
11	Day 10	21.6	21.6	21.6	21.6	24.8	34.0	34.0	37.4	36.6	21.6
12	Day 3	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
13	Day 5	5.6	2.9	2.9	4.1	3.7	7.4	8.5	8.4	8.0	2.9
Sheet1

Cell Formulas
Range	Formula
L2	=SUM(F2:K2)

 

[CodeNinja]

Its ugly but...

=SUM(OFFSET(A2,0,MID(A2,FIND(" ",A2)+1,LEN(A2)),,11-MID(A2,FIND(" ",A2)+1,LEN(A2))))

I'm sure someone will come up with a more elegant formula, but this works.

CN

 

[bbott]

Another option, also not that pretty...Enter in L2 and copy down.

=SUM(INDIRECT(ADDRESS(ROW(),MATCH(A2,$A$1:$K$1,0),4)&":K"&ROW()))

 

[Special-K99]

Maybe (slightly untested)

=SUM(INDIRECT(CHAR(65+MATCH(A2,B$1:K$1,0))&ROW()&":I"&ROW()))

 

[markoakes]

Both formulas worked great, Thanks