Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cross refrence an index table and sum in one step?
Currently I get financial info broken down into accounts, the number accounts
may change every month. I have an index spreadsheet that lists every possible account and what category it goes into. Currently I do a Vlookup from the financial spreadsheet to my index to get the category name on the financial spread sheet, then sumif at the bottom to summarize, not a big deal really, but I have multiple spreadsheets I do this on every month so I was wondering if there was any way possible to do this in one step. Something like a sumif(vlookup(a1:a100,indextable a1:b1000,2,false),categoryname,b1:b100) where a1:a100 is my account name range and b1:b100 is my dollar amounts I know the above doesnt work becacuse vlookup cant use a range, but its the general gist of what I would like to do. I've done some neat things with sumproduct, but since my index range is not the same size as my account range, I know it wont work. Is there some other neat trick to get this to work in one single equation? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cross refrence an index table and sum in one step?
Maybe something like this...
This is your lookup table in the range F1:G6 A...56 B...3 C...95 D...84 E...60 F...46 These are your account names in the range A1:A5 - C A E D A =SUMPRODUCT(SUMIF(F1:F6,A1:A5,G1:G6)) -- Biff Microsoft Excel MVP "Robbro" wrote in message ... Currently I get financial info broken down into accounts, the number accounts may change every month. I have an index spreadsheet that lists every possible account and what category it goes into. Currently I do a Vlookup from the financial spreadsheet to my index to get the category name on the financial spread sheet, then sumif at the bottom to summarize, not a big deal really, but I have multiple spreadsheets I do this on every month so I was wondering if there was any way possible to do this in one step. Something like a sumif(vlookup(a1:a100,indextable a1:b1000,2,false),categoryname,b1:b100) where a1:a100 is my account name range and b1:b100 is my dollar amounts I know the above doesnt work becacuse vlookup cant use a range, but its the general gist of what I would like to do. I've done some neat things with sumproduct, but since my index range is not the same size as my account range, I know it wont work. Is there some other neat trick to get this to work in one single equation? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cross refrence an index table and sum in one step?
I think I described my situation poorly. My lookup table is as follows
A Empl. Welfare B Empl. Welfare C Var Ohead D Fixed Ohead E Empl. Welfare ..... My Financials are as follows A 100 C 200 D 75 E 90 I then insert into the financials a vlookup to put Empl. Wellfare next to A and E, Var Ohead next to C and Fixed Ohead next to D Then below that I lay my categories out Empl. Welfare Var Ohead Fixed Ohead and use sumif to add up all of each category, this works ok, but I was looking to eliminate a step and just have one function in the bottom do the lookup to see what category each account goes into and sum them up in one step. I've tried an array but when I try to use vlookup="Empl. Welfare" it apparently returns true for EVERYTHING if just one item in my range is Empl. Welfare. {=SUM(IF(VLOOKUP(A10:A100,'[Income Statement crossreference.xlsb]Sheet1'!$A$10:$B$107,2,FALSE)=A112,1,0)*B10:B100)} The above sums everything in the range B10:B100, if this worked I would have my solution, I feel I'm close to it, but not sure its possible to ever get it to work? "T. Valko" wrote: Maybe something like this... This is your lookup table in the range F1:G6 A...56 B...3 C...95 D...84 E...60 F...46 These are your account names in the range A1:A5 - C A E D A =SUMPRODUCT(SUMIF(F1:F6,A1:A5,G1:G6)) -- Biff Microsoft Excel MVP "Robbro" wrote in message ... Currently I get financial info broken down into accounts, the number accounts may change every month. I have an index spreadsheet that lists every possible account and what category it goes into. Currently I do a Vlookup from the financial spreadsheet to my index to get the category name on the financial spread sheet, then sumif at the bottom to summarize, not a big deal really, but I have multiple spreadsheets I do this on every month so I was wondering if there was any way possible to do this in one step. Something like a sumif(vlookup(a1:a100,indextable a1:b1000,2,false),categoryname,b1:b100) where a1:a100 is my account name range and b1:b100 is my dollar amounts I know the above doesnt work becacuse vlookup cant use a range, but its the general gist of what I would like to do. I've done some neat things with sumproduct, but since my index range is not the same size as my account range, I know it wont work. Is there some other neat trick to get this to work in one single equation? . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cross refrence an index table and sum in one step?
Sorry, but I'm still not understanding this.
Based on your posted sample data: A Empl. Welfare B Empl. Welfare C Var Ohead D Fixed Ohead E Empl. Welfare A 100 C 200 D 75 E 90 Are you "looking up" A,B,C,D,E from A,C,D,E ? -- Biff Microsoft Excel MVP "Robbro" wrote in message ... I think I described my situation poorly. My lookup table is as follows A Empl. Welfare B Empl. Welfare C Var Ohead D Fixed Ohead E Empl. Welfare .... My Financials are as follows A 100 C 200 D 75 E 90 I then insert into the financials a vlookup to put Empl. Wellfare next to A and E, Var Ohead next to C and Fixed Ohead next to D Then below that I lay my categories out Empl. Welfare Var Ohead Fixed Ohead and use sumif to add up all of each category, this works ok, but I was looking to eliminate a step and just have one function in the bottom do the lookup to see what category each account goes into and sum them up in one step. I've tried an array but when I try to use vlookup="Empl. Welfare" it apparently returns true for EVERYTHING if just one item in my range is Empl. Welfare. {=SUM(IF(VLOOKUP(A10:A100,'[Income Statement crossreference.xlsb]Sheet1'!$A$10:$B$107,2,FALSE)=A112,1,0)*B10:B100)} The above sums everything in the range B10:B100, if this worked I would have my solution, I feel I'm close to it, but not sure its possible to ever get it to work? "T. Valko" wrote: Maybe something like this... This is your lookup table in the range F1:G6 A...56 B...3 C...95 D...84 E...60 F...46 These are your account names in the range A1:A5 - C A E D A =SUMPRODUCT(SUMIF(F1:F6,A1:A5,G1:G6)) -- Biff Microsoft Excel MVP "Robbro" wrote in message ... Currently I get financial info broken down into accounts, the number accounts may change every month. I have an index spreadsheet that lists every possible account and what category it goes into. Currently I do a Vlookup from the financial spreadsheet to my index to get the category name on the financial spread sheet, then sumif at the bottom to summarize, not a big deal really, but I have multiple spreadsheets I do this on every month so I was wondering if there was any way possible to do this in one step. Something like a sumif(vlookup(a1:a100,indextable a1:b1000,2,false),categoryname,b1:b100) where a1:a100 is my account name range and b1:b100 is my dollar amounts I know the above doesnt work becacuse vlookup cant use a range, but its the general gist of what I would like to do. I've done some neat things with sumproduct, but since my index range is not the same size as my account range, I know it wont work. Is there some other neat trick to get this to work in one single equation? . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cross refrence an index table and sum in one step?
I'm looking up account A to see that it goes into my category of Empl
Welfare. Multiple accounts will go into each category. I have generally around 100 accounts (varies from month to month) and about 15 catgories, each account goes into only 1 category, thats what my vlookup to my index table tells me. Then at bottom I summarize by listing each category and using sumif based on the category assigned to the account to add up all accounts that go into that specific category. I was just looking for a way to eliminate inserting vlookups into the financial data and have everything taken care of in 1 step In other words an equation I can put to the right of the following categories that will look up each and sum each account that goes into that category per my index table If vlookup worked in an array, this is what I think would work.... however it only evaluates vlookup once apparently, not once for everything in the range of A10:A100 ={SUM(IF(VLOOKUP(A10:A100,'[Income Statement crossreference.xlsb]Sheet1'!$A$10:$B$107,2,FALSE)=A112,1,0)*B10:B100)} The above sums EVERYTHING in B10:B100 instead of just those for which my vlookup=a112, just wondering if there was a different way to skin this cat. Been looking to try some match or sumproduct.... but nothing ever seems to work. "T. Valko" wrote: Sorry, but I'm still not understanding this. Based on your posted sample data: A Empl. Welfare B Empl. Welfare C Var Ohead D Fixed Ohead E Empl. Welfare A 100 C 200 D 75 E 90 Are you "looking up" A,B,C,D,E from A,C,D,E ? -- Biff Microsoft Excel MVP "Robbro" wrote in message ... I think I described my situation poorly. My lookup table is as follows A Empl. Welfare B Empl. Welfare C Var Ohead D Fixed Ohead E Empl. Welfare .... My Financials are as follows A 100 C 200 D 75 E 90 I then insert into the financials a vlookup to put Empl. Wellfare next to A and E, Var Ohead next to C and Fixed Ohead next to D Then below that I lay my categories out Empl. Welfare Var Ohead Fixed Ohead and use sumif to add up all of each category, this works ok, but I was looking to eliminate a step and just have one function in the bottom do the lookup to see what category each account goes into and sum them up in one step. I've tried an array but when I try to use vlookup="Empl. Welfare" it apparently returns true for EVERYTHING if just one item in my range is Empl. Welfare. {=SUM(IF(VLOOKUP(A10:A100,'[Income Statement crossreference.xlsb]Sheet1'!$A$10:$B$107,2,FALSE)=A112,1,0)*B10:B100)} The above sums everything in the range B10:B100, if this worked I would have my solution, I feel I'm close to it, but not sure its possible to ever get it to work? "T. Valko" wrote: Maybe something like this... This is your lookup table in the range F1:G6 A...56 B...3 C...95 D...84 E...60 F...46 These are your account names in the range A1:A5 - C A E D A =SUMPRODUCT(SUMIF(F1:F6,A1:A5,G1:G6)) -- Biff Microsoft Excel MVP "Robbro" wrote in message ... Currently I get financial info broken down into accounts, the number accounts may change every month. I have an index spreadsheet that lists every possible account and what category it goes into. Currently I do a Vlookup from the financial spreadsheet to my index to get the category name on the financial spread sheet, then sumif at the bottom to summarize, not a big deal really, but I have multiple spreadsheets I do this on every month so I was wondering if there was any way possible to do this in one step. Something like a sumif(vlookup(a1:a100,indextable a1:b1000,2,false),categoryname,b1:b100) where a1:a100 is my account name range and b1:b100 is my dollar amounts I know the above doesnt work becacuse vlookup cant use a range, but its the general gist of what I would like to do. I've done some neat things with sumproduct, but since my index range is not the same size as my account range, I know it wont work. Is there some other neat trick to get this to work in one single equation? . . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cross refrence an index table and sum in one step?
I'm sure this can be done but I'm having a hard time visualizing your setup.
If I could see the file I'm sure we can get this to work. If you want to put together a *small* sample file and send it to me I'll see what I can do. Just make sure you include the results you expect. If you want to do that you can contact me at: xl can help at comcast period net Remove "can" and change the obvious. I have both Excel 2002 and 2007 so either of those file formats will be fine. -- Biff Microsoft Excel MVP "Robbro" wrote in message ... I'm looking up account A to see that it goes into my category of Empl Welfare. Multiple accounts will go into each category. I have generally around 100 accounts (varies from month to month) and about 15 catgories, each account goes into only 1 category, thats what my vlookup to my index table tells me. Then at bottom I summarize by listing each category and using sumif based on the category assigned to the account to add up all accounts that go into that specific category. I was just looking for a way to eliminate inserting vlookups into the financial data and have everything taken care of in 1 step In other words an equation I can put to the right of the following categories that will look up each and sum each account that goes into that category per my index table If vlookup worked in an array, this is what I think would work.... however it only evaluates vlookup once apparently, not once for everything in the range of A10:A100 ={SUM(IF(VLOOKUP(A10:A100,'[Income Statement crossreference.xlsb]Sheet1'!$A$10:$B$107,2,FALSE)=A112,1,0)*B10:B100)} The above sums EVERYTHING in B10:B100 instead of just those for which my vlookup=a112, just wondering if there was a different way to skin this cat. Been looking to try some match or sumproduct.... but nothing ever seems to work. "T. Valko" wrote: Sorry, but I'm still not understanding this. Based on your posted sample data: A Empl. Welfare B Empl. Welfare C Var Ohead D Fixed Ohead E Empl. Welfare A 100 C 200 D 75 E 90 Are you "looking up" A,B,C,D,E from A,C,D,E ? -- Biff Microsoft Excel MVP "Robbro" wrote in message ... I think I described my situation poorly. My lookup table is as follows A Empl. Welfare B Empl. Welfare C Var Ohead D Fixed Ohead E Empl. Welfare .... My Financials are as follows A 100 C 200 D 75 E 90 I then insert into the financials a vlookup to put Empl. Wellfare next to A and E, Var Ohead next to C and Fixed Ohead next to D Then below that I lay my categories out Empl. Welfare Var Ohead Fixed Ohead and use sumif to add up all of each category, this works ok, but I was looking to eliminate a step and just have one function in the bottom do the lookup to see what category each account goes into and sum them up in one step. I've tried an array but when I try to use vlookup="Empl. Welfare" it apparently returns true for EVERYTHING if just one item in my range is Empl. Welfare. {=SUM(IF(VLOOKUP(A10:A100,'[Income Statement crossreference.xlsb]Sheet1'!$A$10:$B$107,2,FALSE)=A112,1,0)*B10:B100)} The above sums everything in the range B10:B100, if this worked I would have my solution, I feel I'm close to it, but not sure its possible to ever get it to work? "T. Valko" wrote: Maybe something like this... This is your lookup table in the range F1:G6 A...56 B...3 C...95 D...84 E...60 F...46 These are your account names in the range A1:A5 - C A E D A =SUMPRODUCT(SUMIF(F1:F6,A1:A5,G1:G6)) -- Biff Microsoft Excel MVP "Robbro" wrote in message ... Currently I get financial info broken down into accounts, the number accounts may change every month. I have an index spreadsheet that lists every possible account and what category it goes into. Currently I do a Vlookup from the financial spreadsheet to my index to get the category name on the financial spread sheet, then sumif at the bottom to summarize, not a big deal really, but I have multiple spreadsheets I do this on every month so I was wondering if there was any way possible to do this in one step. Something like a sumif(vlookup(a1:a100,indextable a1:b1000,2,false),categoryname,b1:b100) where a1:a100 is my account name range and b1:b100 is my dollar amounts I know the above doesnt work becacuse vlookup cant use a range, but its the general gist of what I would like to do. I've done some neat things with sumproduct, but since my index range is not the same size as my account range, I know it wont work. Is there some other neat trick to get this to work in one single equation? . . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cross refrence an index table and sum in one step?
Thanks for the offer, but I've gotten a solution, though not what I
originally wanted, its perfectly functional. I copied my index into each file and look up from the account in that to the financials to get the $ amounts I need then summarize, I was basically trying to do the opposite originally. This takes a lot of space up on the sheet, but its only a summary sheet so thats ok. "T. Valko" wrote: I'm sure this can be done but I'm having a hard time visualizing your setup. If I could see the file I'm sure we can get this to work. If you want to put together a *small* sample file and send it to me I'll see what I can do. Just make sure you include the results you expect. If you want to do that you can contact me at: xl can help at comcast period net Remove "can" and change the obvious. I have both Excel 2002 and 2007 so either of those file formats will be fine. -- Biff Microsoft Excel MVP "Robbro" wrote in message ... I'm looking up account A to see that it goes into my category of Empl Welfare. Multiple accounts will go into each category. I have generally around 100 accounts (varies from month to month) and about 15 catgories, each account goes into only 1 category, thats what my vlookup to my index table tells me. Then at bottom I summarize by listing each category and using sumif based on the category assigned to the account to add up all accounts that go into that specific category. I was just looking for a way to eliminate inserting vlookups into the financial data and have everything taken care of in 1 step In other words an equation I can put to the right of the following categories that will look up each and sum each account that goes into that category per my index table If vlookup worked in an array, this is what I think would work.... however it only evaluates vlookup once apparently, not once for everything in the range of A10:A100 ={SUM(IF(VLOOKUP(A10:A100,'[Income Statement crossreference.xlsb]Sheet1'!$A$10:$B$107,2,FALSE)=A112,1,0)*B10:B100)} The above sums EVERYTHING in B10:B100 instead of just those for which my vlookup=a112, just wondering if there was a different way to skin this cat. Been looking to try some match or sumproduct.... but nothing ever seems to work. "T. Valko" wrote: Sorry, but I'm still not understanding this. Based on your posted sample data: A Empl. Welfare B Empl. Welfare C Var Ohead D Fixed Ohead E Empl. Welfare A 100 C 200 D 75 E 90 Are you "looking up" A,B,C,D,E from A,C,D,E ? -- Biff Microsoft Excel MVP "Robbro" wrote in message ... I think I described my situation poorly. My lookup table is as follows A Empl. Welfare B Empl. Welfare C Var Ohead D Fixed Ohead E Empl. Welfare .... My Financials are as follows A 100 C 200 D 75 E 90 I then insert into the financials a vlookup to put Empl. Wellfare next to A and E, Var Ohead next to C and Fixed Ohead next to D Then below that I lay my categories out Empl. Welfare Var Ohead Fixed Ohead and use sumif to add up all of each category, this works ok, but I was looking to eliminate a step and just have one function in the bottom do the lookup to see what category each account goes into and sum them up in one step. I've tried an array but when I try to use vlookup="Empl. Welfare" it apparently returns true for EVERYTHING if just one item in my range is Empl. Welfare. {=SUM(IF(VLOOKUP(A10:A100,'[Income Statement crossreference.xlsb]Sheet1'!$A$10:$B$107,2,FALSE)=A112,1,0)*B10:B100)} The above sums everything in the range B10:B100, if this worked I would have my solution, I feel I'm close to it, but not sure its possible to ever get it to work? "T. Valko" wrote: Maybe something like this... This is your lookup table in the range F1:G6 A...56 B...3 C...95 D...84 E...60 F...46 These are your account names in the range A1:A5 - C A E D A =SUMPRODUCT(SUMIF(F1:F6,A1:A5,G1:G6)) -- Biff Microsoft Excel MVP "Robbro" wrote in message ... Currently I get financial info broken down into accounts, the number accounts may change every month. I have an index spreadsheet that lists every possible account and what category it goes into. Currently I do a Vlookup from the financial spreadsheet to my index to get the category name on the financial spread sheet, then sumif at the bottom to summarize, not a big deal really, but I have multiple spreadsheets I do this on every month so I was wondering if there was any way possible to do this in one step. Something like a sumif(vlookup(a1:a100,indextable a1:b1000,2,false),categoryname,b1:b100) where a1:a100 is my account name range and b1:b100 is my dollar amounts I know the above doesnt work becacuse vlookup cant use a range, but its the general gist of what I would like to do. I've done some neat things with sumproduct, but since my index range is not the same size as my account range, I know it wont work. Is there some other neat trick to get this to work in one single equation? . . . |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cross refrence an index table and sum in one step?
OK, good luck!
-- Biff Microsoft Excel MVP "Robbro" wrote in message ... Thanks for the offer, but I've gotten a solution, though not what I originally wanted, its perfectly functional. I copied my index into each file and look up from the account in that to the financials to get the $ amounts I need then summarize, I was basically trying to do the opposite originally. This takes a lot of space up on the sheet, but its only a summary sheet so thats ok. "T. Valko" wrote: I'm sure this can be done but I'm having a hard time visualizing your setup. If I could see the file I'm sure we can get this to work. If you want to put together a *small* sample file and send it to me I'll see what I can do. Just make sure you include the results you expect. If you want to do that you can contact me at: xl can help at comcast period net Remove "can" and change the obvious. I have both Excel 2002 and 2007 so either of those file formats will be fine. -- Biff Microsoft Excel MVP "Robbro" wrote in message ... I'm looking up account A to see that it goes into my category of Empl Welfare. Multiple accounts will go into each category. I have generally around 100 accounts (varies from month to month) and about 15 catgories, each account goes into only 1 category, thats what my vlookup to my index table tells me. Then at bottom I summarize by listing each category and using sumif based on the category assigned to the account to add up all accounts that go into that specific category. I was just looking for a way to eliminate inserting vlookups into the financial data and have everything taken care of in 1 step In other words an equation I can put to the right of the following categories that will look up each and sum each account that goes into that category per my index table If vlookup worked in an array, this is what I think would work.... however it only evaluates vlookup once apparently, not once for everything in the range of A10:A100 ={SUM(IF(VLOOKUP(A10:A100,'[Income Statement crossreference.xlsb]Sheet1'!$A$10:$B$107,2,FALSE)=A112,1,0)*B10:B100)} The above sums EVERYTHING in B10:B100 instead of just those for which my vlookup=a112, just wondering if there was a different way to skin this cat. Been looking to try some match or sumproduct.... but nothing ever seems to work. "T. Valko" wrote: Sorry, but I'm still not understanding this. Based on your posted sample data: A Empl. Welfare B Empl. Welfare C Var Ohead D Fixed Ohead E Empl. Welfare A 100 C 200 D 75 E 90 Are you "looking up" A,B,C,D,E from A,C,D,E ? -- Biff Microsoft Excel MVP "Robbro" wrote in message ... I think I described my situation poorly. My lookup table is as follows A Empl. Welfare B Empl. Welfare C Var Ohead D Fixed Ohead E Empl. Welfare .... My Financials are as follows A 100 C 200 D 75 E 90 I then insert into the financials a vlookup to put Empl. Wellfare next to A and E, Var Ohead next to C and Fixed Ohead next to D Then below that I lay my categories out Empl. Welfare Var Ohead Fixed Ohead and use sumif to add up all of each category, this works ok, but I was looking to eliminate a step and just have one function in the bottom do the lookup to see what category each account goes into and sum them up in one step. I've tried an array but when I try to use vlookup="Empl. Welfare" it apparently returns true for EVERYTHING if just one item in my range is Empl. Welfare. {=SUM(IF(VLOOKUP(A10:A100,'[Income Statement crossreference.xlsb]Sheet1'!$A$10:$B$107,2,FALSE)=A112,1,0)*B10:B100)} The above sums everything in the range B10:B100, if this worked I would have my solution, I feel I'm close to it, but not sure its possible to ever get it to work? "T. Valko" wrote: Maybe something like this... This is your lookup table in the range F1:G6 A...56 B...3 C...95 D...84 E...60 F...46 These are your account names in the range A1:A5 - C A E D A =SUMPRODUCT(SUMIF(F1:F6,A1:A5,G1:G6)) -- Biff Microsoft Excel MVP "Robbro" wrote in message ... Currently I get financial info broken down into accounts, the number accounts may change every month. I have an index spreadsheet that lists every possible account and what category it goes into. Currently I do a Vlookup from the financial spreadsheet to my index to get the category name on the financial spread sheet, then sumif at the bottom to summarize, not a big deal really, but I have multiple spreadsheets I do this on every month so I was wondering if there was any way possible to do this in one step. Something like a sumif(vlookup(a1:a100,indextable a1:b1000,2,false),categoryname,b1:b100) where a1:a100 is my account name range and b1:b100 is my dollar amounts I know the above doesnt work becacuse vlookup cant use a range, but its the general gist of what I would like to do. I've done some neat things with sumproduct, but since my index range is not the same size as my account range, I know it wont work. Is there some other neat trick to get this to work in one single equation? . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Drop Down List with Step by Step Instructions for 2007 | Excel Worksheet Functions | |||
Need step by step to add invoice numbering to excel template | New Users to Excel | |||
Index and Match - the next step | Excel Worksheet Functions | |||
What is the step-by-step procedure for making a data list? | Excel Discussion (Misc queries) | |||
I need step by step instructions to create a macro for 10 imbedde. | Excel Worksheet Functions |