LinkBack URL
About LinkBacks
how can i get a list of subtotals?
-via135
how can i get a list of subtotals?
-via135
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
i am afraid u r unable to understand my question?
-via135
Originally Posted by keithl816
Try clarifying your question, what do you mean by your question?
how can i get a 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
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
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
"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(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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"via135" <[email protected]> wrote in
message news:[email protected]...
>
> 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
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
--
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks