which formula works

[sammie9r]

B/H

Monday	Tuesday	Wednesday	Thursday	Friday	TOTAL HOURS	SICK	ANNUAL LEAVE	Total hrs remaining
09:00 - 17:00	sick	09:00 - 17:00	09:00 - 17:00	22.5	7.5	7.5	278

Hi I'm new to excel i am trying to do a formula that would automatically put 7,5 hours in the sick column if someone went sick on a particular day  i got so far as =if(G1="SICK",7.5"0" 

It works for one day but if they were sick for the whole week i need it to total 37.5 hours anyone help please

[#2]

@sammie9r 

=COUNTIF(A2:F2,"sick")*7.5

Is this the forumla you are looking for? 

[#3]

Hi Nearly there but the total for one weeks sick should be 37.5 hours 7,5 hours per day not 67.5 hours

[#4]

@sammie9r 

=COUNTIF(A2:E2,"sick")*F2

Please try formula above.

[#5]

Thank you i also need the annual leave column to do the same thing but you have bank holiday and A/L how do you get them both to work in the same cell

[#6]

@sammie9r 

=278-COUNTIF(A2:E2,"A/L")*F2

I assume that a day A/L reduces the Annual Leave by 7,5 hours and a bank holiday doesn't.

[#7]

Hi i entered =COUNTIF(C16:K16,"sick")*7.5 and it came up with 60 hours