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

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



.

http://www.mrexcel.com/articles.shtml

Scroll down to "Formulas In Excel" and, in particular, pay attention to
the articles on Array formulas.


--
nbrcrunch

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

Rag,

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

"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

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

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

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

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jack Sons" wrote in message
...
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