CODEWITHSUNDEEP
excelexcel-formula
spittingfire
spittingfire

Reputation: 111

IF(AND statement with multiple criterias

I am trying to write a formula to include multiple criteria and can't seem to get it right.

The formula works as is however I need to include "SHOT10","SHOT20", "SH15" and "SH20" 

=IF(AND(C5194="SHOT15",H5194="",I5194=""),E5194,"")

Can someone assist me with modifying the above formula?

Upvotes: 0

Views: 987

Answers (2)

user1274820
user1274820

Reputation: 8144

The AND(C5194="SHOT15",H5194="",I5194="") is equivalent to saying:

C5194="SHOT15" And H5194="" And 15194=""

So what you have in VBA code is:

If C5194="SHOT15" And H5194="" And 15194="" Then
    ActiveCell = E5194
Else
    ActiveCell = ""
End

You can use AND( and OR( to specify different parameters.

For example, If I want to pickup 3 different values in 'A1', but make sure that 'B1' and 'C1' are blank, I can use:

=IF(AND(OR(A1="A",A1="B",A1="C"),B1="",C1=""),"True","False")

So in your case specifically:

The issue now is that I now need to also consider SHOT10, SHOT20, SH15 and SH20 as well. Meaning that if either SHOT15, SHOT10, SHOT20, SH15 or SH20 appears in C5194 and H5194 is blank and I5194 is also blank then return the value of E5194 else return blank. The key is that all the conditions must be met for the value of E5194 be returned

Your formula becomes:

=IF(AND(OR(C5194="SHOT15",C5194="SHOT10",C5194="SHOT20",C5194="SH15",C5194="SH20"),H5194="",I5194=""),E5194,"")

Edit: Shorten Using an array constant per barry houdini:

=IF(AND(OR(C5194={"SHOT15","SHOT10","SHOT20","SH15","SH20"}),H5194="",I5194=""),E5194,"")

Upvotes: 3

NextInLine
NextInLine

Reputation: 2204

=IF(
    AND(
        OR( C5194="SHOT10", C5194="SHOT15", C5194="SHOT20", C5194="SH15", C5194="SH20" ),
        H5194="",
        I5194=""
       ),
    E5194,
    ""
   )

Upvotes: 1

Related Questions