Combining multiple IF conditions

[lesliewheeler]

Hi Friends,

I am trying to build a formula with multiple if conditions, in isolation they work perfectly but when joined results are not achieved.

I need your help to join and make the below into 1 formula

Condition 1: =IF(F4=0,-2000)
Condition 2: =IF(AND(C4="B",D4="W"),IF(COUNTIF($S$3:$S$14, F4),500,IF(COUNTIF($T$3:$T$14, F4),500,800
Condition 3: =IF(AND(C4="R",D4="W"),IF(COUNTIF($S$3:$S$14, F4),500,IF(COUNTIF($U$3:$U$14, F4),500,800
Condition 4: =IF(AND(D4="L",B4="R"),IF(COUNTIF($S$3:$S$14,F4),-500,IF(COUNTIF($T$3:$T$14, F4),-500,IF(OR(F4=30,F4=21),-2000,800
Condition 5: =IF(AND(D4="L",B4="B"),IF(COUNTIF($S$3:$S$14, F4),-500,IF(COUNTIF($U$3:$U$14, F4),-500,IF(OR(F4=29, F4=2),-2000,800

The flow:-
It will check condition 1 first if false then check condition 2 and return results as above if true, else condition 3 and so on so forth.

Will be really glad if someone can help me.

Thanks in advance
Leslie


VBA Or Formula any will work

 

[RCBricker]

Re: Combining multiple IF conditions- Need Help Pls

I am pretty sure this does what you want.

=IF(OR(F4=0,AND(D4="L",B4="R",OR(F4=30,F4=21)),AND(D4="L",B4="B",OR(F4=29,F4=2))),-2000,
IF(AND(D4="W",OR(C4="B",C4="R"),COUNTIF($S$3:$U$14,F4)),500,
IF(AND(D4="L",OR(B4="R",B4="B"),COUNTIF($S$3:$U$14,F4)),-500,800)))

 

[lesliewheeler]

Re: Combining multiple IF conditions- Need Help Pls

Nope sir this doesnt work....

The condition when it comes to "L" it check the other cell has "B" or "R".

If it is "R" then it checks range 2 columns "S" and "T" and returns -500 if TRUE, else check for 2 values(mentioned 30,21) present in the range "U" if present returns -2000, if not present returns 800,

If it is "B" then it checks range 2 columns "S" and "U" and returns -500 if TRUE, else check for 2 values(mentioned 29,2) present in the range "T" if present returns -2000, if not present returns 800

I am pretty sure this does what you want.

=IF(OR(F4=0,AND(D4="L",B4="R",OR(F4=30,F4=21)),AND(D4="L",B4="B",OR(F4=29,F4=2))),-2000,
IF(AND(D4="W",OR(C4="B",C4="R"),COUNTIF($S$3:$U$14,F4)),500,
IF(AND(D4="L",OR(B4="R",B4="B"),COUNTIF($S$3:$U$14,F4)),-500,800)))

 

[RCBricker]

Re: Combining multiple IF conditions- Need Help Pls

Nope sir this doesnt work....

The condition when it comes to "L" it check the other cell has "B" or "R".

If it is "R" then it checks range 2 columns "S" and "T" and returns -500 if TRUE, else check for 2 values(mentioned 30,21) present in the range "U" if present returns -2000, if not present returns 800,

If it is "B" then it checks range 2 columns "S" and "U" and returns -500 if TRUE, else check for 2 values(mentioned 29,2) present in the range "T" if present returns -2000, if not present returns 800

Let me see if I have it this time

=IF(F4=0,-2000,
IF(AND(D4="W",OR(C4="B",C4="R"),COUNTIF($S$3:$U$14,F4)),500,
IF(AND(D4="L",OR(B4="R",B4="B"),COUNTIF($S$3:$U$14,F4)),-500,
IF(AND(D4="L",B4="R",AND(OR(F4=30,F4=21),COUNTIF($T$3:$T$14,F4))),-2000,
IF(AND(D4="L",B4="B",AND(OR(F4=29,F4=2),COUNTIF($U$3:$U$14,F4))),-2000,
800)))))

 

[lesliewheeler]

Re: Combining multiple IF conditions- Need Help Pls

This Didnt work either but it has helped me to figure out where I was going wrong, studied your condition and modified as per my requirement and it did solve the issue.

THANK YOU SO MUCH FOR THE HELP!!!!

Cheers

Leslie

Let me see if I have it this time

=IF(F4=0,-2000,
IF(AND(D4="W",OR(C4="B",C4="R"),COUNTIF($S$3:$U$14,F4)),500,
IF(AND(D4="L",OR(B4="R",B4="B"),COUNTIF($S$3:$U$14,F4)),-500,
IF(AND(D4="L",B4="R",AND(OR(F4=30,F4=21),COUNTIF($T$3:$T$14,F4))),-2000,
IF(AND(D4="L",B4="B",AND(OR(F4=29,F4=2),COUNTIF($U$3:$U$14,F4))),-2000,
800)))))

 

[RCBricker]

Re: Combining multiple IF conditions- Need Help Pls

glad I could help. Any chance you can post the completed formula?