Dynamically sum row values based on column headers? - Excel -

need solutions queries posted in below link,

http://www.mrexcel.com/forum/excel-questions/334740-dynamically-sum-row-values-based-column-headers.html

having personweek separate column sum of weeks should displayed.

quest: dynamically sum row values based on column headers?

it form re-post question in entirety on site; nevertheless:

what looking relatively straight forward. there lot of ways accomplish suggest use combination of 'match' function , the 'offset' function. offset function creates range based on given start point, moving up/down / left/right indicated, given height / width. example:

=offset(b2,1,2,3,4)

indicates range d3:g5. range given starting @ cell b2, moving 1 row down , 2 columns right, , going total of 3 rows , 4 columns.

so remaining point determine start , stop offset.

first, first field in offset function cell a1, top-left corner of data table. find how many rows move down, need find project referring (i assume cell a6 enter project name care about, a7 enter first week care about, , a8 enter last week care about). find how many rows move down a1, then, use match:

=match(a6, a2:a5,0)

to find how many columns move right find first week, use match again:

=match(a7, b1:g1,0)

assuming want @ single project, know how high want range (1).

to find how wide want range be, need know ending week, less starting week:

=(match(a8,b1:g1,0)-match(a7, b1:g1,0))

so whole thing be:

=offset(a1,match(a6, a2:a5,0),match(a7, b1:g1,0),1,match(a8,b1:g1,0)-match(a7, b1:g1,0))

now thing left wrap newly defined range in 'sum' function, so:

=sum(offset(a1,match(a6, a2:a5,0),match(a7, b1:g1,0),1,match(a8,b1:g1,0)-match(a7, b1:g1,0)))

this formula create error if enters project name / weekname in way isn't found in table - in cells a6-a8 may want use data validation allow names entered - let me know if elaboration on that.