if cell contains VAC then add 8

[suehig]

Hi ~ Need a bit of help. I have a spreadsheet where I am trying to incorporate an accrual usage calendar with columns that will calculate the usage based upon what is accrual is typed in a particular day of the month (cell).

I have this so far ... =IF(OR(J7="VAC", L7="VAC", N7="VAC"),(8+8+8), "") which works great at looking at the cells for VAC, but I can not figure out how to only add the 8 if there is a VAC in the cell. Currently if I have only one date with VAC it gives me a total of 24 and I only want it to give me 8.

I hope that makes sense. I was trying to attach a screenshot so you could see, but I can not figure that out ....

 

[philwojo]

Your formula, as written, says that if you find "VAC" in either J7, L7 or N7 to then do (8+8+8). So if any of them have "VAC" in it you at 8 to itself 3 times, so you will always get 24.

You could try this if Columns K and M won't ever have "VAC" in them:

=IF(COUNTIF(J7:N7,"VAC")>0,COUNTIF(J7:N7,"VAC")*8,"")

 

[suehig]

Thank you so much! That worked great. One more quick question Can I apply the OR if I want to check to see if they put a different accrual in the cell .. such as SICK?

 

[philwojo]

There might be a better way to do this, but its Friday and my brain isn't going 100%, but try this:

=IF((COUNTIF(J7:N7,"VAC")+COUNTIF(J7:N7,"SICK"))>0,(COUNTIF(J7:N7,"VAC")+COUNTIF(J7:N7,"SICK"))*8,"")

 

[suehig]

Thank you for this info. I think I am going to sick with the original logic you helped me with ... just 1 accrual at a time.

If I use =IF(COUNTIF(J7:N7,"VAC")>0,COUNTIF(J7:N7,"VAC")*8,"") is it possible for it to look at additional cells in a different row? Something like =IF(COUNTIF(J7:N7,B9:H9,"VAC")>0,COUNTIF(J7:N7,B9:H9,"VAC")*8,"")
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">=IF(COUNTIF(J7:N7,"VAC")>0,COUNTIF(J7:N7,"VAC")*8,"")</body>****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">=IF(COUNTIF(J7:N7,"VAC")>0,COUNTIF(J7:N7,"VAC")*8,"")=IF(COUNTIF(J7:N7,"VAC")>0,COUNTIF(J7:N7,"VAC")*8,"")=IF(COUNTIF(J7:N7,"VAC")>0,COUNTIF(J7:N7,"VAC")*8,"")=IF(COUNTIF(J7:N7,"VAC")>0,COUNTIF(J7:N7,"VAC")*8,"")</body>

 

[philwojo]

Did the one I provided with "SICK" not work for you?

 

[suehig]

Yes it did. But I think I going to break the accruals out separately into their own columns, so I can use the original if statement . I wish I could provide a screen shot of what my worksheet looks like....

 

[suehig]

Hi again ~ I see what you were trying to say. Yes that posted will work. Sorry about that. Monday morning

 

[philwojo]

No problem at all, it basically allows you to type in VAC or SICK to the same set of cells you started with and it will treat either as a day out and add 8 hours to your count.

But if you need something else post back.

 

[suehig]

Thank you again! Have a great day!!