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

C

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.
 
D

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) ?
 
C

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
 
M

Morrigan

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

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


Hope it helps.
 
C

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.
 
C

canix

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

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


Hope it helps.
Click to expand...

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