check occurrence of items in a range

[Jack Sons]

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"},D516,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

 

[Jason Morin]

Try:

=SUMPRODUCT(N(COUNTIF(D5:E16,
{"apple";"pear";"cherry";"coconut";"apricot"})>=1))=5

HTH
Jason
Atlanta, GA

-----Original 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"},D516,0
))=5

works but
=COUNT(MATCH

({"apple", "pear", "cherry"; "coconut","apricot"},D5:E16,0
))=5

 

[RagDyeR]

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!
==============================================


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"},D516,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

 

[Jack Sons]

Rag,

I now saw via the link you gave me (http://tinyurl.com/6r97z) your answer in
the Text formula thread:
From: "RagDyeR" <[email protected]>
Date: Tue, 15 Feb 2005 03:45:14 -0800
Local: Tues, Feb 15 2005 3:45 am
Subject: Re: Text fomula

Strange enough it does not appear in the thread if I open it in the
excel.misc NG.
I tried you solution and saw that AND(COUNTIF(({list})) does the trick.

Thank you very much.

Jack.

 

[Jack Sons]

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]

Afraid I don't understand your question.
Would you care to re-phrase it, and post back?

 

[Jack Sons]

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]

If I understand what you're looking for, why don't you just enter the values
in a column, and individually poll the list with separate Countif formulas?
H1 to H3 = Apple, Pears, Cherry
In I1 enter:
=COUNTIF($D$6:$D$16,H1)
And copy down to I3
That would end up giving you a display of, for example:
Apple 2
Pears 1
Cherry 3

 

[Jack Sons]

Rag,

I allready did so. But what I like is to retrieve the {.....} that you get
when you use te F9 key. That could be useful in other cases also.
When the question of retrieving the {......} part is solved, the next
question is how to "break down" the list (or array?) in its elements, so how
to get
1
2
5
from {1, 2, 5}

Jack.