auto suming
 

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?

auto suming
 

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?

auto suming
 

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?

auto suming
 

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?

auto suming
 

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?

auto suming
 

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?

auto suming
 

thanks alot. to add on, is there anyway to format the total cell. i want it
to be bold..

"Stefi" wrote:

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?

auto suming
 

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
Rows(Columns("E:E").End(xlDown).Row).Font.Bold = True
Columns("E:E").EntireColumn.Hidden = True
End Sub


Regards,
Stefi

€žviolet€� ezt Ã*rta:

thanks alot. to add on, is there anyway to format the total cell. i want it
to be bold..

"Stefi" wrote:

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?

auto suming
 

it doen not work as what i work...it bold the data instead...

"Stefi" wrote:

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
Rows(Columns("E:E").End(xlDown).Row).Font.Bold = True
Columns("E:E").EntireColumn.Hidden = True
End Sub


Regards,
Stefi

€žviolet€� ezt Ã*rta:

thanks alot. to add on, is there anyway to format the total cell. i want it
to be bold..

"Stefi" wrote:

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?

auto suming
 

Give more details, e.g. example data you worked with! For me it works
perfectly with data given in your first posting. Check there are no empty
rows between "name" and "others" rows.

Stefi


€žviolet€� ezt Ã*rta:

it doen not work as what i work...it bold the data instead...

"Stefi" wrote:

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
Rows(Columns("E:E").End(xlDown).Row).Font.Bold = True
Columns("E:E").EntireColumn.Hidden = True
End Sub


Regards,
Stefi

€žviolet€� ezt Ã*rta:

thanks alot. to add on, is there anyway to format the total cell. i want it
to be bold..

"Stefi" wrote:

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?