Summing Criteria with Index - Match

[djcmisc]

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

 

[Harald Staff]

Hi

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

HTH. Best wishes Harald

 

[cardan]

Hi

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

HTH. Best wishes Harald

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

 

[Roger Govier]

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

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

 

[cardan]

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










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

The message was checked by ESET Smart Security.

http://www.eset.com

I did SUMIFS but I don't think I had the right syntax, however I think
I may have figured it out with a SUMPRODUCT. Here is the formula I
am using which seems to work.

=SUMPRODUCT(('2Q 2009'!$E$7:$BD$7=$C31)*('2Q 2009'!$A$8:$A$7000=$C
$3)*('2Q 2009'!$E$8:$BD$7000))

The first set finds and matches the accounting code across the top.
The second set finds and matches the state. The third is the data
range. I actually tried this before but it was not working. I must
have not had the correct ranges or possibly not in the correct
order.