Sums with multiple conditions

[johnclark]

I am looking to create a spreadsheet that calculates sums with conditions ...

In Column C,I want the value to be Column A + Column B, unless it is greater than 8, then I want the sum to return 8.

In Column D, I want the value to be Column C - 8, if the value is between 0 and 4. If the value is greater than 4, then I want the sum to return 4.

In Column E, I want the value to be Column D - 12, with a maximum value of 12.

To put it into perspective, I'm creating a timesheet and I want it to calculate regular hours (max 8hrs), overtime hours (max 4hrs), and doubletime hours (max 12).

 

[Scott R]

C2 = min(8,a2+b2)
d2 = min(4,a2+b2-c2)
e2 = min(12,a2+b2-c2-d2)

 

[jasonb75]

Alternatively

C2 =MEDIAN(0,A2+B2,8)
D2 =MEDIAN(0,(A2+B2)-8,4)
E2 =MEDIAN(0,(A2+B2)-12,12)