Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup on a subtotal
On Sheet 1, I have a listing of stores. I have an auto filter on there so I
can filter by Account name and get back a listing of stores. I'm using the the subtotal to get back a count of all the stores listed. On Sheet 2, I have all the Account names and their total number of stores. I'd like to get a % of stores retrieved using the subtotal. So if on Sheet 1, I filter on ABC Stores and get back 6 and on Sheet 2 is shows that ABC Stores has 10 stores, how can I get the % (6/10 or 66%)? I'm assuming I can just do a vlookup based upon the value of the Account name, but how do I tell the formula to use the Account name that I filtered the table on? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup on a subtotal
You are correct, VLOOKUP is what you need: in Sheet 1, say it is cell
F6 that shows the 6 stores and that A6 contains the code 'ABC Stores'. Then the following will give you the percentage: =F6/VLOOKUP(A6,'Sheet 2'!A:B,2,0) Here I assume that acct names and total number of stores are in columns A:B of 'Sheet 2'. HTH Kostis Vezerides On Apr 9, 9:03 pm, Christa wrote: On Sheet 1, I have a listing of stores. I have an auto filter on there so I can filter by Account name and get back a listing of stores. I'm using the the subtotal to get back a count of all the stores listed. On Sheet 2, I have all the Account names and their total number of stores. I'd like to get a % of stores retrieved using the subtotal. So if on Sheet 1, I filter on ABC Stores and get back 6 and on Sheet 2 is shows that ABC Stores has 10 stores, how can I get the % (6/10 or 66%)? I'm assuming I can just do a vlookup based upon the value of the Account name, but how do I tell the formula to use the Account name that I filtered the table on? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup on a subtotal
yeah - but I'm not always looking at A6. ABC Stores might be in A6, but if I
change my Auto Filter to pull XYZ Mart instead, that may be A55. How can I always tell the formula to look at whatever the results are from my Auto Filter? "vezerid" wrote: You are correct, VLOOKUP is what you need: in Sheet 1, say it is cell F6 that shows the 6 stores and that A6 contains the code 'ABC Stores'. Then the following will give you the percentage: =F6/VLOOKUP(A6,'Sheet 2'!A:B,2,0) Here I assume that acct names and total number of stores are in columns A:B of 'Sheet 2'. HTH Kostis Vezerides On Apr 9, 9:03 pm, Christa wrote: On Sheet 1, I have a listing of stores. I have an auto filter on there so I can filter by Account name and get back a listing of stores. I'm using the the subtotal to get back a count of all the stores listed. On Sheet 2, I have all the Account names and their total number of stores. I'd like to get a % of stores retrieved using the subtotal. So if on Sheet 1, I filter on ABC Stores and get back 6 and on Sheet 2 is shows that ABC Stores has 10 stores, how can I get the % (6/10 or 66%)? I'm assuming I can just do a vlookup based upon the value of the Account name, but how do I tell the formula to use the Account name that I filtered the table on? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pasting to subtotal lines without replacing hidden -non-subtotal l | Excel Discussion (Misc queries) | |||
Bolding the subtotal lines automaticlly When using the Subtotal fu | New Users to Excel | |||
copy subtotal value only - also can be lookup from other worksheet | Excel Discussion (Misc queries) | |||
Lookup ADDRESS of GETPIVOTDATA Subtotal/Cell | Excel Worksheet Functions | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions |