SumIF

[Guest]

I currently have a function that I would like to tweak a little.
Here is the scenario:
Example: Actual Hours Total Comptime Overtime
AE AJ AL AM
169 180 12 0
Comp time is given for the first three Actual hours over 168. After 171
actual hours it goes to overtime. However, if total time exceeds 168 and
actual hours does not exceed 171, I need it to calculate comptime for hours
over 168. When I do this now it automatically goes to 3 in the AL column.

Here is the formula currenty in use.
=IF(AE6<171,MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168)))

Any ideas?
It is confusing to even try to explain it let alone understand it.
Thanks
JP

 

[Guest]

=IF(AND(AJ6>168,AE6<171),MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168)))

 

[Guest]

You are a genius.

Thanks for the help

=IF(AND(AJ6>168,AE6<171),MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168)))

 

[Guest]

My pleasure!

You are a genius.

Thanks for the help