If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
list of subtotals!
thks Dave, Ron & Max.!.!.! all your suggestions worked nicely! but it seems that Dave's is very simple! -via135 Max Wrote: 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 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=499188 |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Address book and Contacts under Folder List View seem to conflict. | Bill (IOS) | Contacts | 5 | January 6th, 2006 09:48 AM |
Sorting Groups in a Report | liz217 | General Discussion | 4 | July 5th, 2005 09:51 PM |
synchronizing form and list box | Deb Smith | Using Forms | 8 | June 21st, 2004 08:15 PM |
finding & sorting unqiue names list | iwtci | Worksheet Functions | 6 | June 6th, 2004 06:43 AM |
How to make list of unique values? | JulieD | Worksheet Functions | 1 | February 26th, 2004 12:25 PM |