Why is my IF statement not working?

rob51852

Board Regular
Joined
Jun 27, 2016
Messages
185
Office Version
  1. 2016
Platform
  1. Windows
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 show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,073
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 23, 2021
Messages
1,073
Office Version
  1. 365
Platform
  1. Windows
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","")),"")
 
Solution

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,103
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Forum statistics

Threads
1,136,275
Messages
5,674,783
Members
419,524
Latest member
helensesc

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top