Why is my IF statement not working?

[rob51852]

Hi,

I am struggling to get this part of my formula to fire:

IF($E$2="In",IF(AND(A5<>"",COUNTIF($F$5:$F$25,"<3")>=3,F5<=2,Z5<>"",Z5>4),"F0.2",""))

Can anyone see what I've done wrong? Here's the full formula:

=IF($E$2="In",IF(AND(A5<>"",COUNTIF($F$5:$F$25,"<2")>=2,F5<=2,Z5<>"",Z5>3),"F0.2",IF($E$2="In",IF(AND(A5<>"",COUNTIF($F$5:$F$25,"<3")>=3,F5<=2,Z5<>"",Z5>4),"F0.2",""))))

Thanks

 

[Alex Blakenburg]

The part that is not "firing" is the True part of a second IF($E$2="In", statement.
But it in the False part of the first IF($E$2="In", statement so when you get there you have already determined that the statement is False so it is never going to execute.

 

[Alex Blakenburg]

Try this, I have consolidated the A5<>"" since it seems to common to all. I wasn't sure if the F5 would change so I left that alone.

=IF(AND($E$2="In",A5<>""),
IF(AND(COUNTIF($F$5:$F$25,"<2")>=2,F5<=2,Z5<>"",Z5>3),"F0.2",
IF(AND(COUNTIF($F$5:$F$25,"<3")>=3,F5<=2,Z5<>"",Z5>4),"F0.2","")),"")

 

[RoryA]

You've already tested to see if E2 is "In" so there isn't a lot of point in doing that again. It looks like you're missing a few arguments for the False parts of some IF statements. Perhaps you meant something like:

=IF($E$2="In",IF(AND(A5<>"",F5<=2,Z5<>""),IF(OR(AND(COUNTIF($F$5:$F$25,"<2")>=2,Z5>3),AND(COUNTIF($F$5:$F$25,"<3")>=3,Z5>4)),"F0.2",""),""),"")

since you seem to test the same conditions for some parts.

 

[rob51852]

Thanks guys, much appreciated

 

[Alex Blakenburg]

Thanks guys, much appreciated

Thanks for letting us know. Glad we could help.