i have the data in this format

name Jan Feb Wed
alan 5 10 15
ben 6 0 3


others 5 10 5
others 1 2 2

what i want is to do 3 suming..one is after the list of name i want it to do
a sum then after which is sum for others. finally do a sum for both name and
other. this is what it shld look like.

name jan feb march
alan 5 10 15
ben 6 0 3
sum 11 10 18

other 5 10 15
other 1 2 2
sum 6 12 17
total 17 22 35

is that a way to do this?

Use a helper column, (say E), insert a header in E1 (say "control"), insert
formula =IF(A2="others",A2,"name") in E2 and drag it down as necessary, then
Data/Subtotals/Group by: control, Function: Sum, Columns to sum:
Jan,Feb,Wed/OK

Regards,
Stefi



€žviolet€� ezt Ã*rta:

i have the data in this format

name Jan Feb Wed
alan 5 10 15
ben 6 0 3


others 5 10 5
others 1 2 2

what i want is to do 3 suming..one is after the list of name i want it to do
a sum then after which is sum for others. finally do a sum for both name and
other. this is what it shld look like.

name jan feb march
alan 5 10 15
ben 6 0 3
sum 11 10 18

other 5 10 15
other 1 2 2
sum 6 12 17
total 17 22 35

is that a way to do this?

m i able to hide this control column? another thing is that hw i can write my
own heading. because it will auto write bla bla sub total..but that nt what i
want..

"Stefi" wrote:

Use a helper column, (say E), insert a header in E1 (say "control"), insert
formula =IF(A2="others",A2,"name") in E2 and drag it down as necessary, then
Data/Subtotals/Group by: control, Function: Sum, Columns to sum:
Jan,Feb,Wed/OK

Regards,
Stefi



€žviolet€� ezt Ã*rta:

i have the data in this format

name Jan Feb Wed
alan 5 10 15
ben 6 0 3


others 5 10 5
others 1 2 2

what i want is to do 3 suming..one is after the list of name i want it to do
a sum then after which is sum for others. finally do a sum for both name and
other. this is what it shld look like.

name jan feb march
alan 5 10 15
ben 6 0 3
sum 11 10 18

other 5 10 15
other 1 2 2
sum 6 12 17
total 17 22 35

is that a way to do this?

1. Yes, you can hide the control column.
2. This is your result table (before hiding the control column):
name Jan Feb Wed control
alan 5 10 15 name
ben 6 0 3 name
11 10 18 name Subtotal
others 5 10 5 others
others 1 2 2 others
6 12 7 others Subtotal
17 22 25 Total

As you can see, it writes the words in Subtotal rows that you used in the
=IF(A2="others",A2,"name") function.

If you hide the control column, the result will be displayed like that:

name Jan Feb Wed
alan 5 10 15
ben 6 0 3
11 10 18
others 5 10 5
others 1 2 2
6 12 7
17 22 25

Regards,
Stefi

€žviolet€� ezt Ã*rta:

m i able to hide this control column? another thing is that hw i can write my
own heading. because it will auto write bla bla sub total..but that nt what i
want..

"Stefi" wrote:

Use a helper column, (say E), insert a header in E1 (say "control"), insert
formula =IF(A2="others",A2,"name") in E2 and drag it down as necessary, then
Data/Subtotals/Group by: control, Function: Sum, Columns to sum:
Jan,Feb,Wed/OK

Regards,
Stefi



€žviolet€� ezt Ã*rta:

i have the data in this format

name Jan Feb Wed
alan 5 10 15
ben 6 0 3


others 5 10 5
others 1 2 2

what i want is to do 3 suming..one is after the list of name i want it to do
a sum then after which is sum for others. finally do a sum for both name and
other. this is what it shld look like.

name jan feb march
alan 5 10 15
ben 6 0 3
sum 11 10 18

other 5 10 15
other 1 2 2
sum 6 12 17
total 17 22 35

is that a way to do this?

it actually nv generate name subtotal or what..it become other field sub
total. anyway is that a way of using vba to code it? cos i want this step to
be done in a click.

"Stefi" wrote:

1. Yes, you can hide the control column.
2. This is your result table (before hiding the control column):
name Jan Feb Wed control
alan 5 10 15 name
ben 6 0 3 name
11 10 18 name Subtotal
others 5 10 5 others
others 1 2 2 others
6 12 7 others Subtotal
17 22 25 Total

As you can see, it writes the words in Subtotal rows that you used in the
=IF(A2="others",A2,"name") function.

If you hide the control column, the result will be displayed like that:

name Jan Feb Wed
alan 5 10 15
ben 6 0 3
11 10 18
others 5 10 5
others 1 2 2
6 12 7
17 22 25

Regards,
Stefi

€žviolet€� ezt Ã*rta:

m i able to hide this control column? another thing is that hw i can write my
own heading. because it will auto write bla bla sub total..but that nt what i
want..

"Stefi" wrote:

Use a helper column, (say E), insert a header in E1 (say "control"), insert
formula =IF(A2="others",A2,"name") in E2 and drag it down as necessary, then
Data/Subtotals/Group by: control, Function: Sum, Columns to sum:
Jan,Feb,Wed/OK

Regards,
Stefi



€žviolet€� ezt Ã*rta:

i have the data in this format

name Jan Feb Wed
alan 5 10 15
ben 6 0 3


others 5 10 5
others 1 2 2

what i want is to do 3 suming..one is after the list of name i want it to do
a sum then after which is sum for others. finally do a sum for both name and
other. this is what it shld look like.

name jan feb march
alan 5 10 15
ben 6 0 3
sum 11 10 18

other 5 10 15
other 1 2 2
sum 6 12 17
total 17 22 35

is that a way to do this?

Yes:
Sub SubTot()
Range("E1").Value = "Controls"
lastrow = Columns("A").Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Range("E2").Formula = "=IF(A2=""others"",A2,""name"") "
Range("E2").AutoFill Destination:=Range("E2:E" & lastrow),
Type:=xlFillDefault
Range("A1").Select
Selection.Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(2, 3,
4), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Columns("E:E").EntireColumn.Hidden = True
End Sub

Regards,
Stefi



€žviolet€� ezt Ã*rta:

it actually nv generate name subtotal or what..it become other field sub
total. anyway is that a way of using vba to code it? cos i want this step to
be done in a click.

"Stefi" wrote:

1. Yes, you can hide the control column.
2. This is your result table (before hiding the control column):
name Jan Feb Wed control
alan 5 10 15 name
ben 6 0 3 name
11 10 18 name Subtotal
others 5 10 5 others
others 1 2 2 others
6 12 7 others Subtotal
17 22 25 Total

As you can see, it writes the words in Subtotal rows that you used in the
=IF(A2="others",A2,"name") function.

If you hide the control column, the result will be displayed like that:

name Jan Feb Wed
alan 5 10 15
ben 6 0 3
11 10 18
others 5 10 5
others 1 2 2
6 12 7
17 22 25

Regards,
Stefi

€žviolet€� ezt Ã*rta:

m i able to hide this control column? another thing is that hw i can write my
own heading. because it will auto write bla bla sub total..but that nt what i
want..

"Stefi" wrote:

Use a helper column, (say E), insert a header in E1 (say "control"), insert
formula =IF(A2="others",A2,"name") in E2 and drag it down as necessary, then
Data/Subtotals/Group by: control, Function: Sum, Columns to sum:
Jan,Feb,Wed/OK

Regards,
Stefi



€žviolet€� ezt Ã*rta:

i have the data in this format

name Jan Feb Wed
alan 5 10 15
ben 6 0 3


others 5 10 5
others 1 2 2

what i want is to do 3 suming..one is after the list of name i want it to do
a sum then after which is sum for others. finally do a sum for both name and
other. this is what it shld look like.

name jan feb march
alan 5 10 15
ben 6 0 3
sum 11 10 18

other 5 10 15
other 1 2 2
sum 6 12 17
total 17 22 35

is that a way to do this?