I replied to R without reading the entire thread.

For your question, replace the "OR" and try this:

=AND(COUNTIF(D5:D16,{"apple","pears","cherry"}))

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Jack Sons" wrote in message
...
R,

Thank you very much, it indeed works, also for D5:E16 (what I was looking
for, more that one dimension).

What also works is

=COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:D16,0))=5
but
=COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:E16,0))=5
does not work.

I got stuck with this problem, I really want it solved, also because the
list or array way (how should I call it?), with {......} is so elegant and
easier to use.

Can you or anybody out there in this august newsgroup help (and explain)?

Jack.

"R.VENKATARAMAN" $$$ schreef in bericht
...
strangely it works with


=IF(OR(COUNTIF(D5:D16,"apple"),COUNTIF(D5:D16,"pea rs"),COUNTIF(D5:D16,"cherr
y")),TRUE,FALSE)

i.e without =1

But I could not explain the logic
=============================


R.VENKATARAMAN $$$ wrote in message
...
sorry. this is not working on subsequent experiments
apologise.

R.VENKATARAMAN $$$ wrote in message news:...
try type in H1




=IF(OR(COUNTIF(D5:D16,"apple"),COUNTIF(D5:D16,"pea rs"),COUNTIF(D5:D16,"cherr
y"))=1,TRUE,FALSE)

your data is in d5 to d16.

do you get what you want.

may be tortuous to write .may be bugs in it. try the experiment

====================================
Jack Sons wrote in message
...
Tom,

Suppose in A1:F6 there can be all kinds of things (text, numbers,
blanks,
formulae), but I want to check if "apple", "pear", "cherry";
"coconut"
and
"apricot" each occur at least once. If so a formula in H1 should
return
TRUE, if not FALSE.
I tried
=COUNT(MATCH({"apple", "pear", "cherry";
"coconut","apricot"},A1:F6,0))=5
but it won't work, also when array entered.
Your help will be appreciated.

Jack Sons
The Netherlands



"Tom Ogilvy" schreef in bericht
...
Left of a double quote:
=if(counif(B1,"*apple*")0,"apple","")

--
Regards,
Tom Ogilvy