Excel 2010 - summing column based on MATCH result

uad
uad used Ask the Experts™
on
I have a workbook containing a data sheet of several thousand rows and 100+ columns, and a summary sheet.

What I am trying to do is use the MATCH function on the summary sheet to locate the specific column title on the data sheet, and once found sum the data that corresponds to the specific column.

Is Match the correct function to use?  If so, how would I create the sum once the column is found.
A simplistic example is attached.
example.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Microsoft Excel Expert
Top Expert 2014
Commented:
put this in c2 and drag down

=SUM(INDEX(Data!$1:$1048576,0,MATCH(Table!A2,Data!$1:$1,0)))
example.xlsx
Saurabh Singh Teotia
Top Expert 2015
Commented:
In C2..You can simply use the formula...

=SUMPRODUCT((Data!$B$1:$D$1=Table!A2)*(Data!$B$2:$D$6))

Open in new window


Enclosed your workbook for your reference...

Saurabh...
example.xlsx
uad

Author

Commented:
Split points because with majority going to ProfessorJimJam because both solutions worked, but ProfessorJimJam was the more dynamic approach with the amount of data being processed

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial