TRY:

Assuming scores in columns A to G and SUM is in H:

=IF(SUM(A1:G1)21,42-SUM(A1:G1),SUM(A1:G1))

HTH

"Mike" wrote:

Hi i got a worksheet that counts goals scored in a week and totals them in
the last column using the "sum" function, but what i want it to do is not
allow the total to go over 21, e.g if someone is on 20 goals and there team
scores 3 then i want it to total to 21 then deduct whats left, in this case
it would display a total of 19, one to make it 21 then two deducted, is this
possible please
--
thanks