excel - VBA search column heading in a sheet and return SUM in another sheet -

i datas sheet 1 sheet 2 reference column headings vba.
example:(excel file)

so if want find sum of fun1 person criteria 1 command have go , find heading “sum of fun 1” in sheet 1 , choose datas under criteria 1 , sum in sheet 2 cell d5. (by using column heading reference instead of cell reference. table range a2 : u80. thanks.

public sub match()     thisworkbook.sheets("sheet1").activate   range("sheet2!b3") = application.sum(application.index(range("a:g"), 0, application.match("crit1" & "fun1persona", range("a2:g2"), 0))) end sub 

i have tried codes failed. know havnt include row reference crit1 , iam not sure how apply formula.

can me ? in advance

you formula. i'll assume table in example covers range a1:e10.

first we'll need find correct column using match formula:
=match("fun2persona",$1:$1,0) - return 3 fun2persona in column c.

next need know how many rows in table. assuming criteria in column has no blanks except cell a1 can use counta:
=counta($a:$a)+1 - return 10.

the above 2 formula used few times within final result, easier use helper cells store results (i'll call them columnref , lastrowref readability rather actual cell references).

now set reference first cell , last cell in column c.
=index($1:$1,,columnref) reference header, while =index($1:$1048576,rowref,columnref) reference last cell.

as these can used references , not values =sum(index($1:$1,,columnref):index($1:$1048576,rowref,columnref)) sum in column. it's same writing =sum(c1:c10).

but want use sumif, need reference criteria in column well.
=index($a:$a,rowref) reference last cell in column a, $a$1:index($a:$a,rowref) reference values in column a.

final formula:

the final step stick final formula:
=sumif($a$1:index($a:$a,rowref),"crit1",index($1:$1,,columnref):index($1:$1048576,rowref,columnref))
same writing =sumif($a$1:$a$10,"crit1",$c$1:$c$10)

for vba solution:

public function sumcriteria(funperson string, criteria string) double      dim rtable range     dim rcol range     dim rcriteria range     dim lastrow long     dim lastcol long      'update sheet1 sheet name table.     thisworkbook.worksheets("sheet1")         'you may have change how find last row/column depending         'on data on sheet.         lastrow = .cells(rows.count, 1).end(xlup).row         lastcol = .cells(1, columns.count).end(xltoleft).column         set rtable = .range(.cells(1, 1), .cells(lastrow, lastcol))          'edit:  set table below if it's static size.         'set rtable = .range("a2:u80")          'the first statement finds funperson heading         set rcol = rtable.rows(1).find(what:=funperson, lookin:=xlvalues, lookat:=xlwhole)         if not rcol nothing             sumcriteria = application.worksheetfunction.sumif(rtable.columns(1), criteria, rtable.columns(rcol.column))         else             sumcriteria = cverr(xlerrvalue)         end if     end end function 

this method looks @ column , row 1 dimensions of table , uses sumif count figures.

you can use worksheet formula: =sumcriteria("fun1persona","crit1")
or within vba:

public sub test()     dim double     = sumcriteria("fun1persona", "crit1") end sub