Look for column name in a sheet and calculate the sum and return in another sheet

[calbee]

Hi I have been working with a excel file with lot of datas which is arranged in 2 sheets, So I would like to get datas from sheet 1 to sheet 2 with reference to the column headings. For example:

	function1personA	Function2personA	Function1personB	Function2PesonB
crit1
crit1
crit1
crit1
crit2
crit2
crit2

<tbody>
</tbody>

So if I want to find the sum of function 1 person A with criteria 1 the command have to go and find the heading “sum of function 1” in sheet 1 and choose the datas that are only under criteria 1 and sum it up in sheet 2 cell D5. (By using column heading reference instead of cell reference. The table range is A2 : U80. thanks

 

[Sinon]

I would make the table into an excel table (shortcut CTRL+T). this will create columns with the name in the header so when you want to reference a column you would have something like Table1[function1personA]. You can then use an INDIRECT to reference the column and do the sum. Let's assume you want to sum up column function1personA and you have this name in Sheet2 in cell A1(value in cell A1 is function1personA ). You would then do this:

=SUM(INDIRECT("Table1["&A1&"]"))

As long as A1 has the column name it will sum up that column. If you change the name it will sum up a different column.
Of course you can rename the table so it's not Table1 but anything you want and then just amend the INDIRECT to use the table name. You can use another cell to pull through the table name if that is necessary as well (if you want to pull data from multiple tables and so forth). The possibilities are endless. However INDIRECT will not work if you are referencing a table from a closed workbook.

 

[arthurbr]

Try =SUMPRODUCT((Sheet1!$A$2:$A$8=Sheet2!A2)*(INDEX(Sheet1!$A$2:$E$8,,MATCH(Sheet2!A1,Sheet1!$A$1:$E$1,0))))

where Sheet2!A1 contains the string "function 1 person A" ( without quotes) and Sheet2!A2 "crit 1")

Adapt the ranges to your needs but be sure to not include the header range except in the MATCH part

PS the double comma is not a keyboard hiccup

 

[calbee]

Thanks for the answers @arthurbr and @sinon :D . But i would like to do this in vba. when i tried doing so its showing errors.

Public Sub Match()




ThisWorkbook.Sheets("Sheet1").Activate


Range("Sheet2!B3") = Application.Sum(Application.Index(Range("A:Z"), 0, Application.Match("Industry Crit1" & "Funtion1personA", Range("A1:G1"), 0)))


End Sub



I have tried it codes but it failed. i know that i havnt include the Row reference for crit1 , but iam not sure how to apply that to the formula.


Can anyone help me with this ? Thanks in advance

 

[calbee]

Sry there is a corrrection.
Range("Sheet2!B3") = Application.Sum(Application.Index(Range("A:G"), 0, Application.Match("Crit1" & "Funtion1personA", Range("A1:G1"), 0)))

Thanks

 

[arthurbr]

In the future could you please specify that you want a VBA solution? You're wasting people's time, and I have better things to do than that.