Complex IF function

[dgavin]

How do I write the following in a formula.

If within range Log!D18:E50 there is "A" and U1 >3 and S1<=Controls!$B$1 then "BACK" if not then ""

Thanks

 

[barry houdini]

Try

=IF(AND(COUNTIF(Log!D18:E50,"A")>0,U1 >3,S1<=Controls!$B$1),"BACK","")

 

[dgavin]

I still get "BACK" even if there is no "A" in range Log!D18:E50

If there is no "A" found then i need it to return nothing?

P.S there are also "B" in that range as well.

 

[barry houdini]

No that shouldn't happen, using AND means that all 3 conditions must be TRUE for "BACK" to be returned. A single "A" (or "a") on its own anywhere in the range Log!D18:E50 will satisfy the first condition.

Did you mean to have a two column range - should it be case-sensitive?

 

[dgavin]

Hi I got the range wrong, rather than at looking at both columns I should have been looking at adjacent cells.

=IF(AND(COUNTIF(Log!D18:E18,"A")>0,U1 >3,S1<=Controls!$B$1),"BACK","")

How do I expand on this by adding..

if any cell whole range Log!D18:E50 = "B" and its corresponding cells in C are less than 5 and if U1 >3,S1<=Controls!$B$1 then "BACK"

Thanks

 

[THEGOB]

This looks as though it's another IF in which case you need an or statement. Something along the lines of:


IF(OR(AND(COUNTIF(D18:E18,"B")>1,C18<5,U1>3,S1<=Controls!$B$1) ,AND(COUNTIF(Log!D18:E50,"A")>0,U1 >3,S1<=Controls!$B$1)),"BACK","")

 

[dgavin]

Ok I have changed it slightly but still dosen't work.

=IF(OR(AND(COUNTIF(D18:E18,"B")>1,Dutch!B18<=5,$U$1>3,$S$1<=Controls!$B$1),AND(COUNTIF(Dutch!D18:E18,"A")>0,$U$1>3,$S$1<=Controls!$B$1)),"BACK","")

I get "BACK" when "A" is in the range, but when "B" is in the range and Dutch!B18<=5 I don't get "BACK"

 

[THEGOB]

=IF(OR(AND(COUNTIF(D18:E18,"B")>0,Dutch!B18<=5,$U$1>3,$S$1<=Controls!$B$1),AND(COUNTIF(Dutch!D18:E18,"A")>0,$U$1>3,$S$1<=Controls!$B$1)),"BACK","")


Shouuld be zero not one. Whoops!