Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing days that match a criteria and date | Excel Discussion (Misc queries) | |||
index/match, based on more than one criteria | Excel Discussion (Misc queries) | |||
index and match on 2 criteria | Excel Worksheet Functions | |||
Add a criteria to an Index and Match formula | Excel Discussion (Misc queries) | |||
Summing multiple hits using match, offset and index method | Excel Programming |