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