# Why is my IF statement not working?

#### rob51852

##### Board Regular
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

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Alex Blakenburg

##### Well-known Member
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

##### Well-known Member
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.

Excel Formula:
``````=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

##### MrExcel MVP, Moderator
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:

Excel Formula:
``=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

##### Board Regular
Thanks guys, much appreciated

#### Alex Blakenburg

##### Well-known Member
Thanks guys, much appreciated

Thanks for letting us know. Glad we could help.

Replies
6
Views
81
Replies
2
Views
186
Replies
1
Views
1K
Replies
0
Views
60
Replies
5
Views
139

1,141,309
Messages
5,705,674
Members
421,404
Latest member
Mikecollo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back