Need help with an IF AND function formula

JenMcG

New Member
Joined
Dec 2, 2016
Messages
18
Hello,

I need help making a formula that will perform a calculation based on the following criteria:

Cell L8 must contain the work "Emergency"
and
Cell CV must be less than or equal to 3

If this criteria is met I need to perform the following calculation:

Value of CH880.30%

Otherwise blank

I tried this and it did not work:
=IF(L8="Emergency"&CV8<=3,CH8*0.30%,"")


Please help.

Thanks,
Jen
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

JenMcG

New Member
Joined
Dec 2, 2016
Messages
18
Hello Jen,

Use this
=IF(AND(L8="Emergency",CV8<=3),CH8*0.30%,"")

Thank you. This worked great!

One more question. What if I wanted to add the word "building" as well to this exact formula?

How would that look?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,015
Office Version
  1. 365
Platform
  1. Windows
I think you need to explain this a little more:
Cell L8 must contain the work "Emergency"
Do you mean it must EXACTLY equal the entry, or that word "Emergency" can appear in cell L8 along with other words?

What if I wanted to add the word "building" as well to this exact formula?
And do you mean that L8 must EXACTLY equal "Emergency" or EXACTLY equal "building"?
Or do you mean that L8 may have a bunch of words, and BOTH those words MUST be contained in L8?
Or perhaps L8 contains a bunch of words, and you only need it to contain EITHER of those words?

Please clarify.
 
Last edited:

live_excel

New Member
Joined
Nov 23, 2016
Messages
42

ADVERTISEMENT

=IF(AND(L8="Emergency",L8="building",CV8<=3),CH8*0.30%,"")
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,015
Office Version
  1. 365
Platform
  1. Windows
=IF(AND(L8="Emergency",L8="building",CV8<=3),CH8*0.30%,"")
That will ALWAYS evaluate to False, because it is impossible for L8 to be EXACTLY equal to "Emergency" and EXACTLY equal to "building" at the same time.
 

JenMcG

New Member
Joined
Dec 2, 2016
Messages
18

ADVERTISEMENT

Hello,

Sorry for the confusion. L8 has multiple categories and I would like to make the original formula apply to both Emergency and building. I copied your above example below for clarification.

Or perhaps L8 contains a bunch of words, and you only need it to contain EITHER of those words?- this is what I would like :)

Thank you,
Jen
 

live_excel

New Member
Joined
Nov 23, 2016
Messages
42
ok got it

use this
=IF(AND(OR(L8="Emergency",L8="building"),CV8<=3),CH8*0.30%,"")
 

JenMcG

New Member
Joined
Dec 2, 2016
Messages
18
Hello,

I need help with one last formula.

=IF(AND(L8="Emergency",DD8<=5),CH8*0.04%,"")

The formula above works fine but I want to add 1 additional condition to it. I need it to also confirm the word "closed" is looked under AM8.

Thank you,
Jen
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,167
Members
417,128
Latest member
Xianter

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