If range of cells = x, then subtract a value for each cell in range that contains x

I'm working on a time sheet and I want to create a formula that will check a range of cells for the word "Sick" and add 7 hours for each cell that contains "Sick"

In cell I19 I have the following formula:

=IF(C14="Sick",H19+7,I10-H10)


but I also wants to check cells C15:C18 and add 7 to cell H19 for each of those cells that contains the word "Sick". (so if C14 and C18 both said "Sick", the value in cell I19 would be H19+14) I've tried just creating more IF statements but that doesn't seem to work. Any Ideas? Thanks!

That worked perfectly!!! Thank-you so much! You rock.

---------- Post added at 03:01 PM ---------- Previous post was at 02:10 PM ----------

Further to my earlier question, I used the following formula in cell I19 and it works great

Please Login or Register  to view this content.

=IF(COUNTIF(J12:J18,">0")>0,H19+COUNTIF(J12:J18,">0")*J19+I10,I10+H19)

Please Login or Register  to view this content.

but how would I change it if I want to perform the same function for M12:M18 at the same time? I'm not sure that makes sense, sorry if it's not clear.

Ok I tried that and it didn't quite work.

What I need is to be able to calculate the amount of flex time available based on hours (over or under) worked in a week (cell H19) but discounting hours sick or on vacation. Cell J19 is the total hours of sick time taken in a week and cell M19 is the total number of hours of vacation time used in a week. So what I want to do is check to see if there were any hours recorded for the week as sick time or vacation time.

Example: Assume I have 30 hours of flex time and a regular work week is 35 hours. If I am on vacation or sick one day (7 hours) but then work an extra 2 hours over the next 4 days I want my flex time to increase by 2 hours even though my total hours works shows up as -5 (28-35+2). Does this make sense? I think I may be making this more complicated than in needs to be and am open to suggestions.

Thanks!!