Multiple SUM's in a column

[L. Howard]

With a header in row 1, how do I insert = WorksheetFunction.Sum(Range("C?:C?"))
in each blank and at the bottom of this column?

Where it will sum 1,2,3 in first blank, then 4,5,6 in second blank and 7,8,9,10 at the bottom.

Column could be 40, maybe 50 + rows, need a sum in each blank cell and at the bottom.

Thanks,
Howard

Header
1
2
3

4
5
6

7
8
9
10

 

[Claus Busch]

Hi Howard,

Am Thu, 6 Nov 2014 10:19:48 -0800 (PST) schrieb L. Howard:

Header
1
2
3

4
5
6

7
8
9
10

try:

Sub MultiSum()
Dim i As Long, LRow As Long
Dim myStart As Long
Dim mySum As Double

With ActiveSheet
LRow = .Cells(Rows.Count, 3).End(xlUp).Row
myStart = 2
For i = myStart To LRow
mySum = 0
Do
mySum = mySum + .Cells(i, 3)
i = i + 1
Loop While Len(.Cells(i, 3)) <> 0
.Cells(i, 3) = mySum
myStart = i + 1
Next
End With

End Sub


Regards
Claus B.

 

[Claus Busch]

Hi Howard,

Am Thu, 6 Nov 2014 19:50:48 +0100 schrieb Claus Busch:

Sub MultiSum()

or:

Sub MultiSum2()
Dim i As Long, LRow As Long
Dim mySum As Double

With ActiveSheet
LRow = .Cells(Rows.Count, 3).End(xlUp).Row
For i = 2 To LRow + 1
If Len(.Cells(i, 3)) = 0 Then
.Cells(i, 3) = mySum
.Cells(i, 3).Font.Bold = True
mySum = 0
i = i + 1
End If
mySum = mySum + .Cells(i, 3)
Next
End With
End Sub


Regards
Claus B.

 

[L. Howard]

Indeed! Works very good.

I was for sure heading in the wrong direction in my attempts to solve.

Thanks a ton, Claus.

Off topic:

I saw your name mentioned in response to a query as to who are the top ten Excel MVP's. Name and picture, (name spelled Klaus).

Regards,
Howard

 

[GS]

Howard,
My Invoicing: Simple Bookkeeping app does sums on its 'Summary' sheet
(P&L format) for each account of their respective sub accounts using a
worksheet formula that utilizes a hidden helper column. This sheet
gives both summary and detail views via outlining so the sum amounts
don't duplicate the detail amounts. The primary function is SUMIF(),
which pulls values from another sheet rather than the existing sheet,
but the formula can be made to work either way. (My intent was to have
'Summary' (a read-only sheet) auto-update when changes were made to
either the 'Expenses' sheet or the 'Income' sheet since they were the
'source' for the consolidated values. (Consolidation is monthly,
quarterly, and year-to-date) I can avail a copy of the project workbook
if you're interested, but you'll need the NameManager addin to see the
hidden named formulas.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[L. Howard]

Howard,
My Invoicing: Simple Bookkeeping app does sums on its 'Summary' sheet
(P&L format) for each account of their respective sub accounts using a
worksheet formula that utilizes a hidden helper column. This sheet
gives both summary and detail views via outlining so the sum amounts
don't duplicate the detail amounts. The primary function is SUMIF(),
which pulls values from another sheet rather than the existing sheet,
but the formula can be made to work either way. (My intent was to have
'Summary' (a read-only sheet) auto-update when changes were made to
either the 'Expenses' sheet or the 'Income' sheet since they were the
'source' for the consolidated values. (Consolidation is monthly,
quarterly, and year-to-date) I can avail a copy of the project workbook
if you're interested, but you'll need the NameManager addin to see the
hidden named formulas.

That's probably more horsepower than I need right now for what I'm working on. With the small example I posted, there will also be a need to enter formulas in a column that matches the sample, where the formulas will return the % value of each cell in the sum group and 100% will be in the total sumrow.

But that is a bit down the road until I get the code Claus offered into thefray.

Standby, if you will. My inabilities have no boundaries.<g>

Howard

 

[GS]

That's probably more horsepower than I need right now for what I'm
working on. With the small example I posted, there will also be a
need to enter formulas in a column that matches the sample, where the
formulas will return the % value of each cell in the sum group and
100% will be in the total sum row.

But that is a bit down the road until I get the code Claus offered
into the fray.

Standby, if you will. My inabilities have no boundaries.<g>

Howard

Ah! My workbook also does % both ways:
Income accounts show %TotalIncome and %TotalExpense
Expense accounts show %TotalExpense and %TotalIncome

...for each account/subaccount.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[L. Howard]

Ah! My workbook also does % both ways:
Income accounts show %TotalIncome and %TotalExpense
Expense accounts show %TotalExpense and %TotalIncome

..for each account/subaccount.

Hmmm, okay.

With small mods to Claus' code this:

Status Message Volume
Failed xxxxxxx 1
Failed xxxxxxx 2
Failed xxxxxxx 3
Invalid xxxxxxx 4
Invalid xxxxxxx 5
Success xxxxxxx 6
Success xxxxxxx 7
Success xxxxxxx 8

Becomes this:

Status Message Volume
Failed xxxxxxx 1
Failed xxxxxxx 2
Failed xxxxxxx 3
Failed Tot = 6
Invalid xxxxxxx 4
Invalid xxxxxxx 5
Invalid Tot = 9
Success xxxxxxx 6
Success xxxxxxx 7
Success xxxxxxx 8
Success Tot = 21

Which is Column A, B, C.
So in column D I need a formula that returns the % of the value in C of the respective total.

The rows per total will vary from update to update. So for instances Success may be 7 rows next time and the others will change also. As will the values. (Won't ever just be 1,2,3,4,5 etc.)

I had 'mild' success in getting an entry (not a correct formula) in the cells in column D but it skipped some cells. aaarg!

So, I'm in the head scratching mode for sure.

Howard

 

[L. Howard]

These should show in column C under Volume.

Failed Tot = 6
Invalid Tot = 9
Success Tot = 21

Howard

 

[GS]

These should show in column C under Volume.

Failed Tot = 6
Invalid Tot = 9
Success Tot = 21

Howard

In this case, where the structure is not consistent, a helper column is
needed (IMO)! That's what I use on 'Summary' since subaccounts for any
account will usually not be the same number of values. Also,
col-absolute/row-relative defined names are used. So technically, the
sheet is a structured template by design. But then, that's my standard
approach to most projects anyway and so isn't anything new to me!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Claus Busch]

Hi Howard,

Am Thu, 6 Nov 2014 18:16:45 -0800 (PST) schrieb L. Howard:

Status Message Volume
Failed xxxxxxx 1
Failed xxxxxxx 2
Failed xxxxxxx 3
Failed Tot = 6
Invalid xxxxxxx 4
Invalid xxxxxxx 5
Invalid Tot = 9
Success xxxxxxx 6
Success xxxxxxx 7
Success xxxxxxx 8
Success Tot = 21

Which is Column A, B, C.
So in column D I need a formula that returns the % of the value in C of the respective total.

try:

Sub MultiSum()
Dim LRow As Long, i As Long
Dim mySum As Double, Total As Double
Dim strFormat As String

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = LRow To 2 Step -1
If .Cells(i, 1) <> .Cells(i + 1, 1) Then
.Rows(i + 1).Insert
End If
Next

LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LRow + 1
If Len(.Cells(i, 1)) = 0 Then
Select Case .Cells(i - 1, 1).Value
Case "Failed"
strFormat = "Failed tot = "
Case "Invalid"
strFormat = "Invalid tot = "
Case "Success"
strFormat = "Success tot = "
End Select
.Cells(i, 3) = strFormat & mySum
mySum = 0
i = i + 1
End If
If i > LRow + 1 Then Exit For
mySum = mySum + CDbl(Mid(.Cells(i, 2), _
InStrRev(.Cells(i, 2), " ") + 1))
Total = Total + CDbl(Mid(.Cells(i, 2), _
InStrRev(.Cells(i, 2), " ") + 1))
Next
For i = 2 To LRow + 1
If Len(.Cells(i, 3)) > 0 Then
.Cells(i, 4) = Mid(.Cells(i, 3), InStrRev(.Cells(i, 3), " ") +
1) / Total
.Cells(i, 4).NumberFormat = "0.00%"
End If
Next
End With
End Sub


Regards
Claus B.

 

[Claus Busch]

Hi again,

Am Fri, 7 Nov 2014 09:01:49 +0100 schrieb Claus Busch:

try:

Sub MultiSum()

here now with some comments and Application.Screenupdating:

Sub MultiSum()
Dim LRow As Long, i As Long
Dim mySum As Double, Total As Double
Dim strFormat As String

Application.ScreenUpdating = False
With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
'Insert rows when value in A changes
For i = LRow To 2 Step -1
If .Cells(i, 1) <> .Cells(i + 1, 1) Then
.Rows(i + 1).Insert
End If
Next
'Calculating the sum of each item
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LRow + 1
If Len(.Cells(i, 1)) = 0 Then
.Cells(i, 3) = .Cells(i - 1, 1) & "tot = " & mySum
mySum = 0
i = i + 1
End If
If i > LRow + 1 Then Exit For
'Picks the numbers on the right side of the string and add them
mySum = mySum + CDbl(Mid(.Cells(i, 2), _
InStrRev(.Cells(i, 2), " ") + 1))
'Is calulating the total of all items
Total = Total + CDbl(Mid(.Cells(i, 2), _
InStrRev(.Cells(i, 2), " ") + 1))
Next
'Calculating the % for each total
For i = 2 To LRow + 1
If Len(.Cells(i, 3)) > 0 Then
.Cells(i, 4) = Mid(.Cells(i, 3), InStrRev(.Cells(i, 3), " ") +
1) / Total
.Cells(i, 4).NumberFormat = "0.00%"
End If
Next
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.

 

[Claus Busch]

Hi Howard,

Am Thu, 6 Nov 2014 18:16:45 -0800 (PST) schrieb L. Howard:

Status Message Volume
Failed xxxxxxx 1
Failed xxxxxxx 2
Failed xxxxxxx 3
Invalid xxxxxxx 4
Invalid xxxxxxx 5
Success xxxxxxx 6
Success xxxxxxx 7
Success xxxxxxx 8

I am sorry but I misunderstood your layout. I thought the numbers are in
column B behind the string. That causes that the two previous answers
are wrong.

Try:
Sub MultiSum()
Dim LRow As Long, i As Long
Dim mySum As Double, Total As Double

Application.ScreenUpdating = False
With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
'Insert rows when value in A changes
For i = LRow To 2 Step -1
If .Cells(i, 1) <> .Cells(i + 1, 1) Then
.Rows(i + 1).Insert
End If
Next
'Calculating the sum of each item
'and the % for each total
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
Total = WorksheetFunction.Sum(Range("C:C"))
For i = 2 To LRow + 1
If Len(.Cells(i, 1)) = 0 Then
.Cells(i, 3) = .Cells(i - 1, 1) & " tot = " & mySum
.Cells(i, 4) = mySum / Total
.Cells(i, 4).NumberFormat = "0.00%"
mySum = 0
i = i + 1
End If
If i > LRow + 1 Then Exit For
mySum = mySum + .Cells(i, 3)
Next
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.

 

[L. Howard]

I am sorry but I misunderstood your layout. I thought the numbers are in
column B behind the string. That causes that the two previous answers
are wrong.

Hi Claus,

I caught the layout referring to B column instead of C and fixed it in the previous code.

Am using your corrected code, which works well except I have miss sated how the %'s should display.

I'm working on that now to try to make the column D %'s pertain to each Status category.

Where Failed 1, 2, 3 would be 16.67%, 33.33% and 50.00% with 100% as the total in column D next to column C entry of "Failed tot = 6"

And a new set of %'s for each of the other Status' Invalid and Success.

Howard

 

[Claus Busch]

Hi Howard,

Am Fri, 7 Nov 2014 02:31:32 -0800 (PST) schrieb L. Howard:

Am using your corrected code, which works well except I have miss sated how the %'s should display.

please have a look:

https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "SumAndPercent"
and run the macro


Regards
Claus B.

 

[L. Howard]

please have a look:

Nothing could be better!

Will test it with larger data samples, but really spot on.

There is a need to do the same percentage computations on columns E and F but I will make that happen from your example. If not I'll be back with "hat in hand" looking for help.

Off to give it a go.

That is some spiffy code indeed. Thanks.

Howard