Adding Additional Criteria to Formula

[billybob11]

Hi guys

I have the following formula which fires when 2 values in a range are the same or less than 2:

=IF(T5<>"","CL",IF($E$2="In",IF(AND(A5<>"",COUNTIF($F$5:$F$45,"<2")>=2,O5<=2),"B3","")))

I am trying to add additional criteria so it also fires when 3 values in a range are the same or less than 3, where 4 values in a range are the same or less than 4, 5 values in a range are the same or less than 5 and 6 values in a range are the same or less than 6.

I keep getting myself in a muddle. Can anyone suggest how this can be done?

Thanks

 

[oldbrewer]

I assume you mean EXACTLY three values are the same, or less than 3 so 1.5, 1.6, 1.7 would trigger as would 1.7,1.7,1.7 ?

 

[billybob11]

Hi, sorry I should have been clearer: it should fire where 3 or more values are under 3, 4 or more values are under 4, etc.

Thanks

 

[oldbrewer]

so is that consecutive values or as soon as 3 under 3 have occurred

7,8,3,3,3,9 or 7,3,8,3,9,11,3

 

[billybob11]

Hi, the values in the range change dynamically so the formula should fire whenever the criteria are met like the current version.

Thanks

 

[higrm]

I'm still trying to understand your set up. Your fixed range is $F$5:$F$45, correct? You want to test all 41 of these cells all the time. When you say "fire", you mean you want the expression to be True? Lastly, you want the condition to be true when any of the tests are true, so an OR condition, not only if all the conditions are true, i.e. you don't need an AND condition between the different tests.

So, if you have a named range dRange=$F$5:$F$45 this formula will give you a true result when any of your conditions occur, namely 2 or more cells less than or equal to 2, OR 3 or more cells less than or equal to 3...

=OR(COUNTIF(dRange,"<=2")>=2,COUNTIF(dRange,"<=3")>=3,COUNTIF(dRange,"<=4")>=4,COUNTIF(dRange,"<=5")>=5,COUNTIF(dRange,"<=6")>=6)

If I misunderstood something, please clarify your requirement referencing just the range you are testing. Your original equation has several cell references which you do not clarify.

Cheers,
Higrm