Dynamically sum row values based on column headers?

[cdh]

Hi all,

I have been racking my brain for over an hour, but can't come up with a solution, so I could use some help.

I have a data set that looks something like this (and, unfortunately, changing the row/column orientation is not possible):

And here is some supplementary data:

What I need to be able to do is dynamically sum the costs of a project between 2 given weeks.

For example:
Per the week range in the supplementary data, what is the sum of the Project1 costs between weeks 2 & 5?

The result of which would need to look at the primary table and thus sum C2:F2 (9,516 + 3,807 + 3,157 + 7,418).


I have tried various combinations of INDEX, MATCH, HLOOKUP, SUMPRODUCT, etc., but I seem to be missing something.

Can anyone please shed some light on this?

Thank you!

 

[Jonmo1]

Try this

IN D11 and filled down
=SUM(INDEX($B$2:$H$7,MATCH(A11,$A$2:$A$7,0),MATCH(B11,$B$1:$H$1,0)):INDEX($B$2:$H$7,MATCH($A11,$A$2:$A$7,0),MATCH(C11,$B$1:$H$1,0)))

Hope that helps...

Personal.xls
	A	B	C	D	E	F	G	H
1		Week1	Week2	Week3	Week4	Week5	Week6	Week7
2	Project1	88	66	25	39	44	88	36
3	Project2	41	51	11	70	65	63	20
4	Project3	30	14	70	83	58	2	47
5	Project4	87	58	70	73	35	29	55
6	Project5	13	42	56	33	74	83	70
7	Project6	79	96	20	24	73	81	63
8
9		StartWeek	EndWeek
10
11	Project1	Week1	Week3	179
12	Project2	Week3	Week4	81
13	Project3	Week4	Week7	190
Sheet1