AutoSum >40

[sclarady]

I want 7 cells to be added up and placed in a new cell. If that sum is more then 40 all I want in that sell is 40 and the remainder to be placed in the next cell. How do I do this?

[mancubus]

in J5:

=IF(SUM(C5:I5)>40,40,SUM(C5:I5))

in K5:

=IF(SUM(C5:I5)>40,SUM(C5:I5)-40,0)


copy down to desired row.

[sclarady]

Thank You. I did try the =IF but some people at work said that is not what I needed so I did not work with it much. I almost had it too. I had =IF(C5:I5)>40,J5=40. Thanks again.





in J5:

=IF(SUM(C5:I5)>40,40,SUM(C5:I5))

in K5:

=IF(SUM(C5:I5)>40,SUM(C5:I5)-40,0)


copy down to desired row.

[mancubus]

you are welcome.

[Aussiebear]

I did try the =IF but some people at work said that is not what I needed so I did not work with it much.

What alternative did they suggest?

[Bob Phillips]

Look, no IFs

=MIN(40,SUM(C5:I5))

=MAX(0,SUM(C5:I5)-40)

[mancubus]

Look, no IFs=MIN(40,SUM(C5:I5))=MAX(0,SUM(C5:I5)-40)thanks xld.