Limiting value in cell and adding remainder in another cell

[Shooter66]

What I need is to limit the numerical value in one cell and add anything over that limit in another cell.

Suppose cell A1 uses the sum formula to get its value. I want the sum to be no higher than 50. The balance remaining, whatever it may be, I want added in another SUM formula in cell A2. How can I do that?

Thanks in advance!

[daddylonglegs]

For A1 try

=MIN(50,Sum_formula)

and in A2

=MAX(0,Sum_formula-50)

[MarvinP]

Hi Shooter66 and welcome to the forum,

See if the attached does what you want.

[Shooter66]

Thank you both so much!

Marvin, that is very close to what I need. How would I add another range to the result you have in B1? For instance whatever my balance is in B1, say I want to add A20:A25 to that?

Thanks!

[MarvinP]

Hi Shooter66,

I was thinking that you could use my example and tweak it to fit your exact problem. You can extend the range of cells that get summed in my example to fit your data. If you need another column to be summed you could add it to the right of Col B and subtract A1 and B1. I'd need to see your exact data and problem to give a better answer. I'm thinking you can figure it out from here and then modify it to fit your data.

[Shooter66]

I just need to know the syntax to add another range sum to this =SUM(A2:A20)-A1

If I have to use another cell and then add them, I'll do that. You got me on the right track, I'll figure something out. Thanks!