if cell contains VAC then add 8

suehig

New Member
Joined
Mar 2, 2018
Messages
6
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 ....
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

philwojo

Well-known Member
Joined
May 10, 2013
Messages
533
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:

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

suehig

New Member
Joined
Mar 2, 2018
Messages
6
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

Well-known Member
Joined
May 10, 2013
Messages
533
There might be a better way to do this, but its Friday and my brain isn't going 100%, but try this:

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

suehig

New Member
Joined
Mar 2, 2018
Messages
6

ADVERTISEMENT

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>
 

suehig

New Member
Joined
Mar 2, 2018
Messages
6

ADVERTISEMENT

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

New Member
Joined
Mar 2, 2018
Messages
6
Hi again ~ I see what you were trying to say. Yes that posted will work. Sorry about that. Monday morning :)
 

philwojo

Well-known Member
Joined
May 10, 2013
Messages
533
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,168
Messages
5,594,632
Members
413,919
Latest member
ZaxAlchemist

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top