# Excel formula sum multiple rows/column once on cell has reached it's max number and continue to sum

I am trying to come up with a formula that will split three rows of numbers evenly until one cell reaches a max number and then continue to sum the same three rows but split all the new values in two and no longer give the cell that's reached it's number any more.

example:

``````A1=1
A2=2
A3=3
B1=5
B2=7
B3=9
C1=4
C2=8
C3=4461
D3=20
G1=1500 formula MIN(1500,SUM(A1:E3)/3)
G2=2250 and G3=2250 formula IF(G1=1500,SUM(A1:E3/2),SUM(A1:E3/3))
``````

what I need is once G1 reaches 1500 G2 and G3 to continue to sum but now split everything that's new in two instead of by 3.

For example before the condition was true all three columns are to split these values by 3 and total correctly, once G1 hit 1500 G2 and G3 should continue to sum but split anything new by two because G1 is no longer in need to increase since it's hit the number it needed to. (4520-1500=3020/2=1510).

Is there a way to do this with another formula or to add to what I have existing? What am I doing wrong?

``````=IF(\$G\$1=1500,(SUM(\$A\$1:\$E\$3)-\$G\$1)/2,SUM(\$A\$1:\$E\$3)/3)