On Nov 22, 3:55*pm, "Harald Staff" wrote:
Hi

Don't use formulas. This is what a pivot table does.

HTH. Best wishes Harald

"djcmisc" wrote in message

...

Hello, I am trying to figure a formula that will search a range of
data and sum the values of a specific column based on the column name
and row name. *I have a table of 7000+ companies listed in column B.
Column A is the State where the company is located. *In Row 1, are
accounting codes, each representing things like, Net Income, Revenue,
Expenses etc... There are 60+ fields of codes.

I would like to write a formula that will search the data table,
recognize the column identifiers ("Net Income" for example) and then
sum all those values of all the banks located in New York.

I have been using the Index Match formula to pull out info on each
individual company. Here is an example the formula I have been using.

INDEX('2Q 2009'!$C$2:$BE$7000,MATCH(G$10,'2Q 2009'!$B$2:$B
$7000,0),MATCH($C31,'2Q 2009'!$C$1:$BE$1,0)) *"G$10" = The company
name & $C31 = the accounting code

This works great to find the information on a specific company, as far
as finding and summing the column of all companies in a specific state
is where I am lost. *Summing the Index Match on a specific criteria
would be ideal, as the output sheet for the data is formatted
differently and I have to do this numerous time for each quarter.

Any assistance would be great. *Thanks....

Hi Harald. Thank you for the quick response. The issue is the way I
present the data. I have 6 quarters of data (each 7000x50 table is in
different worksheets) represents a quarter and I need to show all
quarters side by side. I am skeptical of linking cells to a pivot
table. Are my concerns valid? Thanks again..