Excel Table - change the column I'm summing based on a variable

[excel_mills]

I have 2 tables: table 1 which contains a summary; table 2 which contains my data

I want to be able to look up and sum the total of a column in table 2 based on the value in table 1 (ie. I need to be able to change the syntax in the formula "=SUM(table2[column])"

To give an example, here is my first table:

A B*
Apples =sum(table2[@A])
Bananas =sum(table2[@A])
Pears =sum(table2[@A])

In table 2, I have column headings "Apples", "Bananas, "Pears".

Is this even possible? I have tried using the INDIRECT function and "" but none of these seem to work.

I'm relatively new to Excel and keen to learn, so any help would be greatly appreciated. I hope my example of what I'm trying to do makes sense!

Thank you for your help

* Column A contains "Apples", "Pears", etc
* Column B contains my formula

 

[Marcelo Branco]

Maybe...

Formula in B2
=SUM(INDEX(table2,0,MATCH(A2,table2[#Headers],0)))
copy down

Assumes A2=Apples; A3=Bananas;....

M.

 

[Marcelo Branco]

Or

B2
=SUM(INDIRECT("table2["&A2&"]"))
copy down

M.

 

[excel_mills]

Or

B2
=SUM(INDIRECT("table2["&A2&"]"))
copy down

M.

Thank you very much!!

 

[Marcelo Branco]

Thank you very much!!

You are welcome and thanks for the feedback.

M.