list of subtotals!

[via135]

how can i get a list of subtotals?
-via135

 

[keithl816]

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]

i am afraid u r unable to understand my question?
-via135

 

[keithl816]

Try clarifying your question, what do you mean by your question?


how can i get a list of subtotals?

 

[via135]

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]

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

 

[Guest]

using your example, after subtotals have been set up, try this:

D1: name
E1: amount

G1: name
G2: *Total

Select your data range
Data>Filter>Advanced 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

 

[Dave Peterson]

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.

 

[Max]

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(ISNUMBER(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


--

 

[via135]

thks Dave, Ron & Max.!.!.!

all your suggestions worked nicely! but it seems that Dave's is very
simple!

-via135

 

[Max]

You're welcome !

.. but it seems that Dave's is very simple!

Thought you wanted it automated (w/o using vba) <g>

--

 

[Dave Peterson]

Or if the OP wanted the results to be updated when the data changed.

 

[Max]

Or if the OP wanted the results to be updated when the data changed.

Yes, it'll do something like that, too <g>