auto suming

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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:
 
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..
 
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:
 
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.
 
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:
 
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:
 
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:
 
Back
Top