IF Formula 1

[Suggie]

Evening and happy new year,

I have a range of cells (H2:L2)

There are various numbers with these cells.

In cell M2, I've been trying an if formula to help with following but can't figure it for the life of me....

If within the range of H2:L2, the number 35 or 40 appears 3 or MORE times then return, "Yes", if not "No"

Any ideas?

TIA,
Mark

 

[TonyJollans]

Hi Mark,

[blue][tt] =IF(COUNTIF(H2:L2,"A")>=3,"Yes","No")[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[TonyJollans]

Hi Mark,

Sorry, fingers quicker than eyes.

If you want EITHER 3 or more 35s OR 3 or more 40s, then ..

[blue][tt] =IF(OR(COUNTIF(H2:L2,35)>=3,COUNTIF(H2:L2,40)>=3),"Yes","No")[/tt][/blue]

If you want 3 or more (35s or 40s) in TOTAl, then ..

[blue][tt] =IF((COUNTIF(H2:L2,35)+COUNTIF(H2:L2,40))>=3,"Yes","No")[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[Zathras]

It's not very elegant, but it works:
[tt]
=IF(SUM(IF(OR(H2=35,H2=40),1,0),IF(OR(I2=35,I2=40),1,0),IF(OR(J2=35,J2=40),1,0),IF(OR(K2=35,K2=40),1,0),IF(OR(L2=35,L2=40),1,0))>=3,"Yes","No")
[/tt]

 

[ManxDom]

Try this formula in cell M2

=IF(COUNTIF(H2:L2,40)+COUNTIF(H2:L2,35)>=3,"Yes","No")

This formula returns "Yes" if there are 3 or more instances of 35 or 40 in the range H2:L2.

i.e. 35 40 21 40 23 would return "Yes"

The next formula should be used if you want to return a "Yes" if 3 or more instances of the same number exist.

=IF(COUNTIF(H2:L2,40)>=3,"Yes",IF(COUNTIF(H2:L2,35)>=3,"Yes","No"))

i.e. 35 40 35 40 21 would return "No"
35 40 35 35 21 would return "Yes"

Dom

 

[Suggie]

Tony,

Thanks, I've tried if(or to based this on two criteria's
i.e. if the number 35 or 40 appears 3 or More times then Yes

TIA,

 

[Suggie]

Tony,

My fingers also quicker than checking for an update!!!

The If(or formula you have for the either argument works perfect, have a good evening.


Appreciate it,
Mark