Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
list of subtotals!
how can i get a list of subtotals? -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=499188 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
list of subtotals!
Hi Via 135, This was found in excel help. Function_Num Function 1 AVERAGE 2 COUNT 3 COUNTA 4 MAX 5 MIN 6 PRODUCT 7 STDEV 8 STDEVP 9 SUM 10 VAR 11 VARP You can also look at this link http://www.contextures.com/xlFunctions01.html Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://www.excelforum.com/member.php...o&userid=21287 View this thread: http://www.excelforum.com/showthread...hreadid=499188 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
list of subtotals!
i am afraid u r unable to understand my question? -via135 keithl816 Wrote: Hi Via 135, This was found in excel help. Function_Num Function 1 AVERAGE 2 COUNT 3 COUNTA 4 MAX 5 MIN 6 PRODUCT 7 STDEV 8 STDEVP 9 SUM 10 VAR 11 VARP You can also look at this link http://www.contextures.com/xlFunctions01.html Larry -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=499188 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
list of subtotals!
Try clarifying your question, what do you mean by your question? how can i get a list of subtotals? -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://www.excelforum.com/member.php...o&userid=21287 View this thread: http://www.excelforum.com/showthread...hreadid=499188 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
list of subtotals!
COL"A" COL"B" name amount a 10 a 20 a 10 b 40 b 20 c 10 c 10 c 30 d 20 d 30 when you use data|subtotals..for change in "name".. use function "sum" u'll get the results as under: a 40 b 60 c 50 d 50 r u able to understand..? now i want to copy the list of a 40 b 60 c 50 d 50 to another location for sum other calculation purpose.! can u give me the solution? -via135 keithl816 Wrote: Try clarifying your question, what do you mean by your question? how can i get a list of subtotals? -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=499188 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
list of subtotals!
If I understood you correctly (If your subtotal for A is per say in Sheet 1,A5 and you want it in sheet 2, d1). You could put something like this in Sheet 2, D1. =sheet1!a5 Change the sheet name to the name of the sheet you are referencing If it's in another workbook you'll need to change the file path to reflect it Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://www.excelforum.com/member.php...o&userid=21287 View this thread: http://www.excelforum.com/showthread...hreadid=499188 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
list of subtotals!
using your example, after subtotals have been set up, try this:
D1: name E1: amount G1: name G2: *Total Select your data range DataFilterAdvanced Filter Check: Copy to another location List Range: (already selected) Criteria Range: G1:G2 Copy to: D1:E1 Click the [OK] button Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "via135" wrote: COL"A" COL"B" name amount a 10 a 20 a 10 b 40 b 20 c 10 c 10 c 30 d 20 d 30 when you use data|subtotals..for change in "name".. use function "sum" u'll get the results as under: a 40 b 60 c 50 d 50 r u able to understand..? now i want to copy the list of a 40 b 60 c 50 d 50 to another location for sum other calculation purpose.! can u give me the solution? -via135 keithl816 Wrote: Try clarifying your question, what do you mean by your question? how can i get a list of subtotals? -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=499188 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
list of subtotals!
Another play to try, using non-array formulas
Sample construct available at: http://www.savefile.com/files/7026191 Extract_List_of_Subtotals_via135_newusers.xls Assume source table is in Sheet1, with SubTotals applied (Table with subtotals will be in A1:C16) In Sheet2, Put in C1: =IF(AND(ISNUMBER(SEARCH("Total",Sheet1!A1)),NOT(IS NUMBER(SEARCH("Grand Total",Sheet1!A1)))),ROW(),"") Copy C1 down to C16 Put in A1: =IF(ISERROR(SMALL($C:$C,ROW(C1))),"",INDEX(Sheet1! A:A,MATCH(SMALL($C:$C,ROW( C1)),$C:$C,0))) Copy A1 to B1, fill down to B16 Cols A & B will return the desired results, with all lines neatly bunched at the top: a Total 40 b Total 60 c Total 50 d Total 50 And if you want to extract the "Grand Total" line as well (or you don't mind the "Grand Total" line coming in as well) just replace the formula in C1 with the simpler: =IF(ISNUMBER(SEARCH("Total",Sheet1!A1)),ROW(),"") and copy C1 down to C16 (No change to the formulas in cols A & B) This would yield in cols A and B: a Total 40 b Total 60 c Total 50 d Total 50 Grand Total 200 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "via135" wrote in message ... COL"A" COL"B" name amount a 10 a 20 a 10 b 40 b 20 c 10 c 10 c 30 d 20 d 30 when you use data|subtotals..for change in "name".. use function "sum" u'll get the results as under: a 40 b 60 c 50 d 50 r u able to understand..? now i want to copy the list of a 40 b 60 c 50 d 50 to another location for sum other calculation purpose.! can u give me the solution? -via135 keithl816 Wrote: Try clarifying your question, what do you mean by your question? how can i get a list of subtotals? -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=499188 |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
list of subtotals!
After you use data|Subtotals, you can use the outlining symbols on the left hand
side to hide the details. Select the range to copy Edit|goto|Special|visible cells only Copy and paste to the new location. via135 wrote: how can i get a list of subtotals? -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=499188 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List box setup, placement, and functionality | New Users to Excel | |||
multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) | |||
Data Validation and Blanks in List | Excel Worksheet Functions | |||
HOW DO I MAKE VALIDATION LIST CONTAING DATA FROM A DIFFERENT BO | Excel Discussion (Misc queries) | |||
Printing data validation scenarios | Excel Worksheet Functions |