New Contributor

# which formula works

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

6 Replies

# Re: which formula works

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

Is this the forumla you are looking for?

# Re: which formula works

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

# Re: which formula works

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

# Re: which formula works

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

# Re: which formula works

=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.

# Re: which formula works

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