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

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi again,

Am Thu, 6 Nov 2014 19:32:30 +0100 schrieb Claus Busch:

Sub MultiSum()

that you see the sum cells immediately:

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
.Cells(i, 3).Font.Bold = True
myStart = i + 1
Next
End With

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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

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

On Thursday, November 6, 2014 3:24:39 PM UTC-8, GS wrote:
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


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

On Thursday, November 6, 2014 3:24:39 PM UTC-8, GS wrote:
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


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

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

These should show in column C under Volume.

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

Howard

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional