which formula works

Question

B/HMondayTuesdayWednesdayThursdayFridayTOTAL HOURSSICKANNUAL LEAVETotal hrs remaining09:00 - 17:00sick09:00 - 17:0009:00 - 17:0022.57.57.5278&nbsp;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&nbsp; i got so far as =if(G1="SICK",7.5"0"&nbsp;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&nbsp;

oliverscheurich · Answer

sammie9r&nbsp;=COUNTIF(A2:F2,"sick")*7.5Is this the forumla you are looking for?&nbsp;

sammie9r · Answer

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

oliverscheurich · Answer

sammie9r&nbsp;=COUNTIF(A2:E2,"sick")*F2Please try formula above.

sammie9r · Answer

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

oliverscheurich · Answer

sammie9r&nbsp;=278-COUNTIF(A2:E2,"A/L")*F2I assume that a day A/L reduces the Annual Leave by 7,5 hours and a bank holiday doesn't.&nbsp;&nbsp;

Forum Discussion

which formula works

6 Replies

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