Summing Criteria with Index - Match
 

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....

Summing Criteria with Index - Match
 

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....

Summing Criteria with Index - Match
 

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..

Summing Criteria with Index - Match
 

"cardan" wrote in message
...

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..

Hm yes. Multiple worksheets are not ideal for pivot tables, and linking
cells can be pretty awkward.
Would it be possible to gather data into a single sheets, with a Quarter
column added to it?

If not, =SUMIFS will sum by multiple criteria (Excel 2007 and up only)

Best wishes Harald

Summing Criteria with Index - Match
 

Hi Daniel

The way I would tackle this would be to use Pivot Tables along with a VBA
macro.
I would have an extra data sheet with a copy of one of the sheets data, and
from this construct a Dynamic Range for the Data which would be sued as the
source for a Pivot Table which showed the data I wanted.

In a loop in the macro, I would copy each of the 6 sets of data for the 6
sheets in turn, Refresh the Pivot and via VBA copy the data I wanted to my
report sheet in the column for Q1.
Continue the loop until all 6 Quarters have been covered

--

Regards
Roger Govier

"cardan" wrote in message
...
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..

__________ Information from ESET Smart Security, version of virus
signature database 5641 (20101123) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 5641 (20101123) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Summing Criteria with Index - Match
 

On Nov 23, 4:24*am, "Roger Govier"
wrote:
Hi Daniel

The way I would tackle this would be to use Pivot Tables along with a VBA
macro.
I would have an extra data sheet with a copy of one of the sheets data, and
from this construct a Dynamic Range for the Data which would be sued as the
source for a Pivot Table which showed the data I wanted.

In a loop in the macro, I would copy each of the 6 sets of data for the 6
sheets in turn, Refresh the Pivot and via VBA copy the data I wanted to my
report sheet in the column for Q1.
Continue the loop until all 6 Quarters have been covered

--

Regards
Roger Govier

"cardan" wrote in message

...



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.