Excel sum up to a value then reset 3

[OMoore]

Hi all.

I have a column of numbers that I need to sum in the following way. Column B should sum all numbers in column A that are on the same row or preceeding rows. For example from the list below, B1 would sum as 100. B3 as 500 etc. Is there a formula that can do this so that I don't have to manually enter each cell and select all preceeding column A cells to sum?

Finally, I need to sum until the summed number reaches greater than or equal to 700. Once this is reached the sum starts all over again from the next cell. For example cell B4 will sum to 700. Then the sum for B5 will start again with 250. B6 to 500 etc..


A B
1 100
2 100
3 300
4 200 700
5 250
6 250
7 250 750
8 .. etc
9 .. etc

 

[xlbo]

In answer to the 1st question - yes - have a look at absolute & relative referencing. This uses the $ to indicate a fixed placement - you want to fix the top but not the bottom so:

=SUM(A$1:A2) into cell B2 and then copy down

In terms of auto incrementing, you would need to test the result of the sum and then use the ROW() function to get a new start point for the SUM and then use INDIRECT to convert to a proper range

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[KenWright]

Try this:-

B1 - =IF(A1<700,"",A1)
B2 - =IF(SUM($A$1:$A2)<700,"",IF(COUNTA($B$1:$B1)=0,SUM($A$1:$A2),IF(SUM(OFFSET
(A2,,,SUMPRODUCT(MAX((--ROW($B$1:$B1))*($B$1:$B1<>"")))-ROW()))<700,"",SUM
(OFFSET(A2,,,SUMPRODUCT(MAX((--ROW($B$1:$B1))*($B$1:$B1<>"")))-ROW())))))

and then copy down

Regards
Ken...........

-------------------------------------------------------------
It's easier to beg forgiveness than ask permission
-------------------------------------------------------------

 

[AnotherHiggins]

In your example, you only have the >=700 cells visible. But you don't state that this is a requirement. If it is not, then there is a very simple way to do this.

In B1, type in [COLOR=blue white]=A1[/color]
In B2, type in [COLOR=blue white]=IF(B1>=700,A2,B1+A2)[/color]

Drag the formula in B2 down as far as is needed.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[OMoore]

Thanks Guys. The last two methods work (using conditional formatting to hide values below 700 with John's idea).

I was working on the Row() and Indirect() idea for a while. I think it would work but was having huge problems trying to solve it. Learned a lot about row and indirect functions in the process!!

Owen

 

[KenWright]

LOL, always more than one way to skin a cat, and in this instance I think I prefer John's anyway.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------