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.


--
canix
------------------------------------------------------------------------
canix's Profile: http://www.excelforum.com/member.php...o&userid=25962
View this thread: http://www.excelforum.com/showthread...hreadid=393304

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)))*(N OT(ISBLANK(A1:A10)))*1)

maybe if(the above formula0,x,y) ?


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=393304

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


--
canix
------------------------------------------------------------------------
canix's Profile: http://www.excelforum.com/member.php...o&userid=25962
View this thread: http://www.excelforum.com/showthread...hreadid=393304

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

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


Hope it helps.


canix Wrote:
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
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=393304

=COUNTIF(A1:A10,"apple")

I can put the formula like this =COUNTIF(A1:A10,"apple") in cell b1 ,
and have another formula =if(b10,"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
------------------------------------------------------------------------
canix's Profile: http://www.excelforum.com/member.php...o&userid=25962
View this thread: http://www.excelforum.com/showthread...hreadid=393304

Morrigan Wrote:
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.


--
canix
------------------------------------------------------------------------
canix's Profile: http://www.excelforum.com/member.php...o&userid=25962
View this thread: http://www.excelforum.com/showthread...hreadid=393304

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

"canix" wrote:


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.


--
canix
------------------------------------------------------------------------
canix's Profile: http://www.excelforum.com/member.php...o&userid=25962
View this thread: http://www.excelforum.com/showthread...hreadid=393304

canix,

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

HTH,
Bernie
MS Excel MVP


"canix" wrote in message
...

=COUNTIF(A1:A10,"apple")

I can put the formula like this =COUNTIF(A1:A10,"apple") in cell b1 ,
and have another formula =if(b10,"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
------------------------------------------------------------------------
canix's Profile: http://www.excelforum.com/member.php...o&userid=25962
View this thread: http://www.excelforum.com/showthread...hreadid=393304