Need help with an IF AND function formula

JenMcG

New Member
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%,"")

Thanks,
Jen

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

live_excel

New Member
Hello Jen,

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

JenMcG

New Member
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
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?

Last edited:

live_excel

New Member

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

Joe4

MrExcel MVP, Junior Admin
=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

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
ok got it

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

JenMcG

New Member
ok got it

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

Thank you very much! Worked wonderfully.

JenMcG

New Member
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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,747
Messages
5,855,428
Members
431,731
Latest member
Sagar k

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?

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

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