Making a sum reach a minimum value

[jbroberson21]

I previously got some help with this, but the formula isn't working right now due to a change in the sheet. The file is a timesheet. I need cell J21 to total j5 through j18 and I need to make sure the total is a minimum of an average of 8 hours for every cell that has a time in it. For example if we only worked 4 hours a day for two days and that was it, J21 would need to show 16 hours not the 8 actually worked. But if the total is more than the 8 hour average, for example we worked 10 hours for two days J21 would need to be 20 hours. Thanks

[Pete_UK]

I would suggest that you apply your COUNTA to column D, so that it counts the number of days worked, then the formula in J21 will become:

=MAX(SUM(J5:J18),COUNTA(D5:D18)*8/24)

Note that you have to divide by 24 in order to convert the count into hours. It's also a good idea to apply a Custom Format to cells J20 and J21 of [h]:mm, which will prevent the hours wrapping if they are above 24.

Hope this helps.

Pete