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

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

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

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

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

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

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

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

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

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

You're welcome !

.. but it seems that Dave's is very simple!
Thought you wanted it automated (w/o using vba) <g
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"via135" wrote in
message ...

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

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

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

Max wrote:

You're welcome !

.. but it seems that Dave's is very simple!
Thought you wanted it automated (w/o using vba) <g
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"via135" wrote in
message ...

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

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

--

Dave Peterson

"Dave Peterson" wrote:
Or if the OP wanted the results to be updated when the data changed.

Yes, it'll do something like that, too <g
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--