CODEWITHSUNDEEP
excel
DavidJB
DavidJB

Reputation: 2362

Multiple IF AND statements excel

I need to write an "if" statement in Excel based on text in two different cells. 

If E2 ='in play'   and F2 ='closed'      output 3 
If E2= 'in play'   and F2 ='suspended'   output 2
If E2 ='In Play'   and F2 ='Null'        output 1 
If E2 ='Pre-Play'  and F2 ='Null'        output -1
If E2 ='Completed' and F2 ='Closed'      output 2
If E2 ='Suspended' and F2 ='Null'        output 3
If anything else output -2

where Null is no value in the cell

I was trying to do this with the code below but I can't seem to get two or more IF AND statements working together. How can I solve this problem?

=IF(AND(E2="In Play",F2="Closed"),3, -2), IF(AND(E2="In Play",F2=" Suspended"),3,-2)

Upvotes: 10

Views: 447069

Answers (4)

marhsall
marhsall

Reputation: 1

Making these 2 communicate

=IF(OR(AND(MID(K27,6,1)="N",(MID(K27,6,1)="C"),(MID(K27,6,1)="H"),(MID(K27,6,1)="I"),(MID(K27,6,1)="B"),(MID(K27,6,1)="F"),(MID(K27,6,1)="L"),(MID(K27,6,1)="M"),(MID(K27,6,1)="P"),(MID(K27,6,1)="R"),(MID(K27,6,1)="P"),ISTEXT(G27)="61"),AND(RIGHT(K27,2)=G27)),"Good","Review")

=IF(AND(RIGHT(K27,2)=G27),"Good","Review")

Upvotes: -1

qqqq
qqqq

Reputation: 11

Try the following:

=IF(OR(E2="in play",E2="pre play",E2="complete",E2="suspended"),
IF(E2="in play",IF(F2="closed",3,IF(F2="suspended",2,IF(ISBLANK(F2),1,-2))),
IF(E2="pre play",IF(ISBLANK(F2),-1,-2),IF(E2="completed",IF(F2="closed",2,-2),
IF(E2="suspended",IF(ISBLANK(F2),3,-2))))),-2)

Upvotes: 1

barry houdini
barry houdini

Reputation: 46401

With your ANDs you shouldn't have a FALSE value -2, until right at the end, e.g. with just 2 ANDs

=IF(AND(E2="In Play",F2="Closed"),3,IF(AND(E2="In Play",F2=" Suspended"),3,-2))

although it might be better with a combination of nested IFs and ANDs - try like this for the full formula:[Edited - thanks David]

=IF(E2="In Play",IF(F2="Closed",3,IF(F2="Suspended",2,IF(F2="Null",1))),IF(AND(E2="Pre-play",F2="Null"),-1,IF(AND(E2="Completed",F2="Closed"),2,IF(AND(E2="Pre-play",F2="Null"),3,-2))))

To avoid a long formula like the above you could create a table with all E2 possibilities in a column like K2:K5 and all F2 possibilities in a row like L1:N1 then fill in the required results in L2:N5 and use this formula

=INDEX($L$2:$N$5,MATCH(E2,$K$2:$K$5,0),MATCH(F2,$L$1:$N$1,0))

Upvotes: 10

David Zemens
David Zemens

Reputation: 53663

Consider that you have multiple "tests", e.g.,

  1. If E2 = 'in play' and F2 = 'closed', output 3
  2. If E2 = 'in play' and F2 = 'suspended', output 2
  3. Etc.

What you really need to do is put successive tests in the False argument. You're presently trying to separate each test by a comma, and that won't work.

Your first three tests can all be joined in one expression like:

=IF(E2="In Play",IF(F2="Closed",3,IF(F2="suspended",2,IF(F2="Null",1))))

Remembering that each successive test needs to be the nested FALSE argument of the preceding test, you can do this:

=IF(E2="In Play",IF(F2="Closed",3,IF(F2="suspended",2,IF(F2="Null",1))),IF(AND(E2="Pre-Play",F2="Null"),-1,IF(AND(E2="completed",F2="closed"),2,IF(AND(E2="suspended",F2="Null"),3,-2))))

Upvotes: 18

Related Questions