ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   loop and sum (https://www.excelbanter.com/excel-programming/381818-re-loop-sum.html)

Bernie Deitrick
loop and sum
 
Jenna,

Put this formula into cell D2, and copy down to match your column C:

=IF(SUM($C$1:C2)-SUM($D$1:D1) 3500,SUM($C$1:C2)-SUM($D$1:D1),"")

Otherwise, you could use this macro:

Sub SumC()
Dim myC As Range
Dim mySum As Double
mySum = 0
For Each myC In Intersect(Range("C2:C65536"), _
ActiveSheet.UsedRange)
mySum = mySum + myC.Value
If mySum 3500 Then
myC(1, 2).Value = mySum
mySum = 0
End If
Next myC
End Sub

HTH,
Bernie
MS Excel MVP


"jenna" wrote in message
...

Please help

i need a procedure to loop down column c and the first row where the sum is
greater than 3500 put that amount in the adjacent column, then repeat from
the next row down.

thanks



Bernie Deitrick
loop and sum
 
Jenna,

Your summed range at the end:

SUM($C$1:C2)

is wrong. It should be column D:

=IF(SUM($C$1:C3)-SUM($D$1:D2) 3500,SUM($C$1:C3)-SUM($D$1:D2),"")

HTH,
Bernie
MS Excel MVP



"jenna" wrote in message
...
Hii

I would prefer the formula but i'think its a dead end

here's my formula

=IF(SUM($C$1:C3)-SUM($D$1:D2) 3500,SUM($C$1:C3)-SUM($C$1:C2),"")
the problem is after the IF is true i want the formula to start from the
nest row

is possible let me know.

thanks

"Bernie Deitrick" wrote:

Jenna,

Put this formula into cell D2, and copy down to match your column C:

=IF(SUM($C$1:C2)-SUM($D$1:D1) 3500,SUM($C$1:C2)-SUM($D$1:D1),"")

Otherwise, you could use this macro:

Sub SumC()
Dim myC As Range
Dim mySum As Double
mySum = 0
For Each myC In Intersect(Range("C2:C65536"), _
ActiveSheet.UsedRange)
mySum = mySum + myC.Value
If mySum 3500 Then
myC(1, 2).Value = mySum
mySum = 0
End If
Next myC
End Sub

HTH,
Bernie
MS Excel MVP


"jenna" wrote in message
...

Please help

i need a procedure to loop down column c and the first row where the
sum is
greater than 3500 put that amount in the adjacent column, then repeat
from
the next row down.

thanks

Jenna
loop and sum
 

bernie

The only problem is have with the formula is after the test is true i want
that value in column D instead of the total


thanks so far
"Bernie Deitrick" wrote:

Jenna,

Your summed range at the end:

SUM($C$1:C2)

is wrong. It should be column D:

=IF(SUM($C$1:C3)-SUM($D$1:D2) 3500,SUM($C$1:C3)-SUM($D$1:D2),"")

HTH,
Bernie
MS Excel MVP



"jenna" wrote in message
...
Hii

I would prefer the formula but i'think its a dead end

here's my formula

=IF(SUM($C$1:C3)-SUM($D$1:D2) 3500,SUM($C$1:C3)-SUM($C$1:C2),"")
the problem is after the IF is true i want the formula to start from the
nest row

is possible let me know.

thanks

"Bernie Deitrick" wrote:

Jenna,

Put this formula into cell D2, and copy down to match your column C:

=IF(SUM($C$1:C2)-SUM($D$1:D1) 3500,SUM($C$1:C2)-SUM($D$1:D1),"")

Otherwise, you could use this macro:

Sub SumC()
Dim myC As Range
Dim mySum As Double
mySum = 0
For Each myC In Intersect(Range("C2:C65536"), _
ActiveSheet.UsedRange)
mySum = mySum + myC.Value
If mySum 3500 Then
myC(1, 2).Value = mySum
mySum = 0
End If
Next myC
End Sub

HTH,
Bernie
MS Excel MVP


"jenna" wrote in message
...

Please help

i need a procedure to loop down column c and the first row where the
sum is
greater than 3500 put that amount in the adjacent column, then repeat
from
the next row down.

thanks

Bernie Deitrick
loop and sum
 
Jenna,

So, if you have a column filled with just the number 101, so that by row 36 (the 35th value) you
have 3,535. What do you want in column D? 101, or 3,500, or 3,535, or 35? And do you want to
start your sum over with 0, or with the amount that the sum was over 3,500? And what if the sum is
exactly 3,500?

HTH,
Bernie
MS Excel MVP


"jenna" wrote in message
...

bernie

The only problem is have with the formula is after the test is true i want
that value in column D instead of the total


thanks so far
"Bernie Deitrick" wrote:

Jenna,

Your summed range at the end:

SUM($C$1:C2)

is wrong. It should be column D:

=IF(SUM($C$1:C3)-SUM($D$1:D2) 3500,SUM($C$1:C3)-SUM($D$1:D2),"")

HTH,
Bernie
MS Excel MVP



"jenna" wrote in message
...
Hii

I would prefer the formula but i'think its a dead end

here's my formula

=IF(SUM($C$1:C3)-SUM($D$1:D2) 3500,SUM($C$1:C3)-SUM($C$1:C2),"")
the problem is after the IF is true i want the formula to start from the
nest row

is possible let me know.

thanks

"Bernie Deitrick" wrote:

Jenna,

Put this formula into cell D2, and copy down to match your column C:

=IF(SUM($C$1:C2)-SUM($D$1:D1) 3500,SUM($C$1:C2)-SUM($D$1:D1),"")

Otherwise, you could use this macro:

Sub SumC()
Dim myC As Range
Dim mySum As Double
mySum = 0
For Each myC In Intersect(Range("C2:C65536"), _
ActiveSheet.UsedRange)
mySum = mySum + myC.Value
If mySum 3500 Then
myC(1, 2).Value = mySum
mySum = 0
End If
Next myC
End Sub

HTH,
Bernie
MS Excel MVP


"jenna" wrote in message
...

Please help

i need a procedure to loop down column c and the first row where the
sum is
greater than 3500 put that amount in the adjacent column, then repeat
from
the next row down.

thanks

Jenna
loop and sum
 
Hii Bernie

I want 101 in column D and start at 0

Thanks you so much

"Bernie Deitrick" wrote:

Jenna,

So, if you have a column filled with just the number 101, so that by row 36 (the 35th value) you
have 3,535. What do you want in column D? 101, or 3,500, or 3,535, or 35? And do you want to
start your sum over with 0, or with the amount that the sum was over 3,500? And what if the sum is
exactly 3,500?

HTH,
Bernie
MS Excel MVP


"jenna" wrote in message
...

bernie

The only problem is have with the formula is after the test is true i want
that value in column D instead of the total


thanks so far
"Bernie Deitrick" wrote:

Jenna,

Your summed range at the end:

SUM($C$1:C2)

is wrong. It should be column D:

=IF(SUM($C$1:C3)-SUM($D$1:D2) 3500,SUM($C$1:C3)-SUM($D$1:D2),"")

HTH,
Bernie
MS Excel MVP



"jenna" wrote in message
...
Hii

I would prefer the formula but i'think its a dead end

here's my formula

=IF(SUM($C$1:C3)-SUM($D$1:D2) 3500,SUM($C$1:C3)-SUM($C$1:C2),"")
the problem is after the IF is true i want the formula to start from the
nest row

is possible let me know.

thanks

"Bernie Deitrick" wrote:

Jenna,

Put this formula into cell D2, and copy down to match your column C:

=IF(SUM($C$1:C2)-SUM($D$1:D1) 3500,SUM($C$1:C2)-SUM($D$1:D1),"")

Otherwise, you could use this macro:

Sub SumC()
Dim myC As Range
Dim mySum As Double
mySum = 0
For Each myC In Intersect(Range("C2:C65536"), _
ActiveSheet.UsedRange)
mySum = mySum + myC.Value
If mySum 3500 Then
myC(1, 2).Value = mySum
mySum = 0
End If
Next myC
End Sub

HTH,
Bernie
MS Excel MVP


"jenna" wrote in message
...

Please help

i need a procedure to loop down column c and the first row where the
sum is
greater than 3500 put that amount in the adjacent column, then repeat
from
the next row down.

thanks

Bernie Deitrick
loop and sum
 
Jenna,

The easiest thing would be to use column E, with a formula like this
(written for E3):

=IF(D3<"",C3,"")

Copy down to match the formulas in column D, then hide column D.

HTH,
Bernie
MS Excel MVP


"jenna" wrote in message
...
Hii Bernie

I want 101 in column D and start at 0

Thanks you so much

"Bernie Deitrick" wrote:

Jenna,

So, if you have a column filled with just the number 101, so that by row
36 (the 35th value) you
have 3,535. What do you want in column D? 101, or 3,500, or 3,535, or
35? And do you want to
start your sum over with 0, or with the amount that the sum was over
3,500? And what if the sum is
exactly 3,500?

HTH,
Bernie
MS Excel MVP


"jenna" wrote in message
...

bernie

The only problem is have with the formula is after the test is true i
want
that value in column D instead of the total


thanks so far
"Bernie Deitrick" wrote:

Jenna,

Your summed range at the end:

SUM($C$1:C2)

is wrong. It should be column D:

=IF(SUM($C$1:C3)-SUM($D$1:D2) 3500,SUM($C$1:C3)-SUM($D$1:D2),"")

HTH,
Bernie
MS Excel MVP



"jenna" wrote in message
...
Hii

I would prefer the formula but i'think its a dead end

here's my formula

=IF(SUM($C$1:C3)-SUM($D$1:D2) 3500,SUM($C$1:C3)-SUM($C$1:C2),"")
the problem is after the IF is true i want the formula to start from
the
nest row

is possible let me know.

thanks

"Bernie Deitrick" wrote:

Jenna,

Put this formula into cell D2, and copy down to match your column
C:

=IF(SUM($C$1:C2)-SUM($D$1:D1) 3500,SUM($C$1:C2)-SUM($D$1:D1),"")

Otherwise, you could use this macro:

Sub SumC()
Dim myC As Range
Dim mySum As Double
mySum = 0
For Each myC In Intersect(Range("C2:C65536"), _
ActiveSheet.UsedRange)
mySum = mySum + myC.Value
If mySum 3500 Then
myC(1, 2).Value = mySum
mySum = 0
End If
Next myC
End Sub

HTH,
Bernie
MS Excel MVP


"jenna" wrote in message
...

Please help

i need a procedure to loop down column c and the first row where
the
sum is
greater than 3500 put that amount in the adjacent column, then
repeat
from
the next row down.

thanks

Jenna
loop and sum
 
thanks bernie


All times are GMT +1. The time now is 11:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com