Why even use the IF()?
=OR(COUNTIF(D5:D16,"apple"),COUNTIF(D5:D16,"pears" ),COUNTIF(D5:D16,"cherry")
)
OR, even simpler:
=OR(COUNTIF(D5:D16,{"apple","pears","cherry"}))
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------
"R.VENKATARAMAN" $$$ wrote in message
...
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
|