Sum IF function on timesheets

[Karen Brown]

I am designing a timesheet to total hours people work each day into
standard and overtime hours.

Users put the following variables into a sheet:
Project worked on, day of the week, number of hours. Often people will
work on more than one project each day.

I want to auto calculate the number of hours in total for monday -
sunday with the first total being the standard number of hours in total
on the day (7.5) and the 2nd being the total above 7.5 ie overtime
worked.

So Joe Bloggs works
Mon Project 1, 3.5 hours, Project 2, 4 hours and Project 3, 2 hours. He
has worked 9.5 hours in the day.

I want my totals to be 7.5 standard and 2 overtime hours.

I have this formula to calculate the total number of hours each day
(where F is the column they put in the day of the week and I the no of
hours):

=SUMIF(F9:F20,"Mon",I9:I20)

This works for summing the total number of hours per day only.

I thought this might work:

=IF(SUMIF(F9:F20,"Mon",I9:I20)>7.5,7.5,SUM)

but it gives a #NAME error.

Any thoughts on this would be much appreciated.

Many thanks

 

[Jason Morin]

=IF(SUMIF(F9:F20,"Mon",I9:I20)>7.5,7.5,SUMIF
(F9:F20,"Mon",I9:I20))

HTH
Jason
Atlanta, GA