Rag,
=AND(COUNTIF(D5:E16;{"a";"b";"c"}))
can be evaluated by highlighting the COUNTIF(D5:E16;{"a";"b";"c"}) part of
the formula when it showes in the white slot next to the = sign right above
the column headings (A, B, C etc.) and then click function key F9 (return to
normal mode with the esc key). What I see then (right above the column
headings A, B, C etc) is
=AND({1,2,1}) indicating a is found once, b twice and c once in D5:E16. With
ctrl C ctrl V this {1,2,1} can be copied to any cell, but I would like a
formula (perhaps array entered?) that gives me (without copying) in cell H11
{1,2,1} or the elements 1, 2 and 1 below each other for instance in H11, H12
etc.
I hope you can understand it now. Sorry for my English, I never have the
opportunity to speak it, so it will look peculiar, I'm sure.
Jack.
"Ragdyer" schreef in bericht
...
Afraid I don't understand your question.
Would you care to re-phrase it, and post back?
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jack Sons" wrote in message
...
rag,
An aditional question:
If I evaluate the COUNTIF(({list}) part of your function with F9
function
key, it shows a resulting list (array) consisting of numbers that
indicate
how many times each item occurs in the range.
Suppose the AND(COUNTIF(({list})) formula is in H10, which in casu
results
in TRUE, what should I do to get the elements of the resulting list -
which
I meant above - in H11 up to H16 (or als far as it takes with respect to
the
number of items in the original list)?
Jack.
"RagDyeR" schreef in bericht
...
You asked this question 4 days ago, and received some suggestions.
Did you try any of them?
http://tinyurl.com/6r97z
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
"Jack Sons" wrote in message
...
I want to check if "apple", "pear", "cherry"; "coconut" and
"apricot" each occur at least once in a given range.
=COUNT(MATCH({"apple", "pear", "cherry";
"coconut","apricot"},D5:D16,0))=5
works but
=COUNT(MATCH({"apple", "pear", "cherry";
"coconut","apricot"},D5:E16,0))=5
does not work.
I heard that match() works only with a single column or row, so the
second
formula does not work. How to overcome this? Perhaps with an arrayed
function?
I do not want to work with consecutive countif's, because if my list
{....}has 25 items it is very laborious and cumbersom to write 25
countif's.
Jack Sons
The Netherlands