If(b1:B10="Apple","x","")

[canix]

Hello all,
is there any formula to acheive the same result? I know it won't work
that way

b1:b10="Apple" meant any cell in the range contain "Apple" will trigger
the cell on off.

 

[duane]

not sure exactly what you want, but this formula five you a count of the
cells in a1:a10 containing apple

=SUMPRODUCT(NOT(ISNA(SEARCH("apple",A1:A10,1)))*(NOT(ISBLANK(A1:A10)))*1)

maybe if(the above formula>0,x,y) ?

 

[canix]

Thanks for a quick reply,

What I want is very simple, have a formula lookup range A1:A10 to see
if any cell have "apple", if there's "apple" in those range then will
results an X , so that I know in those range there's apple in it. I
don't need to know how many cell which have apple. All I need is if
apple is in those range A1:A10.

it should be very simple

 

[Morrigan]

Just do a simple COUNTIF() or put COUNTIF() inside an IF() statement:

=IF(COUNTIF()>0,"X","no apple")


Hope it helps.

 

[canix]

=COUNTIF(A1:A10,"apple")

I can put the formula like this =COUNTIF(A1:A10,"apple") in cell b1 ,
and have another formula =if(b1>0,"x"',"") in another cell.

The above method should give me the answer, but I want a simple formula
in one cell instead of have two formula laying around, Since this should
be very simple.

 

[canix]

Just do a simple COUNTIF() or put COUNTIF() inside an IF() statement:

=IF(COUNTIF()>0,"X","no apple")


Hope it helps.

Yes...this is exactly what I want, I thought of it but wasn't sure how
the syntax put togeter. Thanks Morrigan.

 

[Guest]

try
=if(or(B1:B10="Apple"),"x","") entered as an array (Control-Shift-Enter)

 

[Bernie Deitrick]

canix,

=IF(COUNTIF(A1:A10,"apple")>0,"x"',"")

HTH,
Bernie
MS Excel MVP