Is there a formula for searching for any one of a list of strings?
 

I am making a template for a series of spreadsheets. I have a list of strings
and a series of spreadsheets which contain varying numbers (or none) of that
list.

E.g. In a series of monthly spreadsheets of crates of fruit bought by a
greengrocer, I want to search for particular types of fruit (apple, kumquat
etc.) to see if he bought any that month.

I can make a formula (COUNTIF...) display if one of them appears, but can't
find a way to count how many from my entire list appear (apple OR kumquat OR
lychee).

Please, can anybody help?

Is there a formula for searching for any one of a list of strings?
 

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A20,{"apple","kumquat","pear"}, 0))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"bookgirl" wrote in message
...
I am making a template for a series of spreadsheets. I have a list of
strings
and a series of spreadsheets which contain varying numbers (or none) of
that
list.

E.g. In a series of monthly spreadsheets of crates of fruit bought by a
greengrocer, I want to search for particular types of fruit (apple,
kumquat
etc.) to see if he bought any that month.

I can make a formula (COUNTIF...) display if one of them appears, but
can't
find a way to count how many from my entire list appear (apple OR kumquat
OR
lychee).

Please, can anybody help?

Is there a formula for searching for any one of a list of strings?
 

=COUNT(1/COUNTIF(A2:A20,{"apple","kumquat","pear"}))

Bob Phillips wrote:

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A20,{"apple","kumquat","pear"}, 0))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"bookgirl" wrote in message
...
I am making a template for a series of spreadsheets. I have a list of
strings
and a series of spreadsheets which contain varying numbers (or none) of
that
list.

E.g. In a series of monthly spreadsheets of crates of fruit bought by a
greengrocer, I want to search for particular types of fruit (apple,
kumquat
etc.) to see if he bought any that month.

I can make a formula (COUNTIF...) display if one of them appears, but
can't
find a way to count how many from my entire list appear (apple OR kumquat
OR
lychee).

Please, can anybody help?

Is there a formula for searching for any one of a list of strings?
 

??????????

=SUMPRODUCT(COUNTIF(A2:A20,{"apple","kumquat","pea r"}))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Lori" wrote in message
oups.com...
=COUNT(1/COUNTIF(A2:A20,{"apple","kumquat","pear"}))

Bob Phillips wrote:

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A20,{"apple","kumquat","pear"}, 0))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"bookgirl" wrote in message
...
I am making a template for a series of spreadsheets. I have a list of
strings
and a series of spreadsheets which contain varying numbers (or none)
of
that
list.

E.g. In a series of monthly spreadsheets of crates of fruit bought by
a
greengrocer, I want to search for particular types of fruit (apple,
kumquat
etc.) to see if he bought any that month.

I can make a formula (COUNTIF...) display if one of them appears, but
can't
find a way to count how many from my entire list appear (apple OR
kumquat
OR
lychee).

Please, can anybody help?

Is there a formula for searching for any one of a list of strings?
 

On Fri, 1 Dec 2006 05:11:00 -0800, bookgirl
wrote:

I am making a template for a series of spreadsheets. I have a list of strings
and a series of spreadsheets which contain varying numbers (or none) of that
list.

E.g. In a series of monthly spreadsheets of crates of fruit bought by a
greengrocer, I want to search for particular types of fruit (apple, kumquat
etc.) to see if he bought any that month.

I can make a formula (COUNTIF...) display if one of them appears, but can't
find a way to count how many from my entire list appear (apple OR kumquat OR
lychee).

Please, can anybody help?

What about:

=countif(rng,"apple") + countif(rng,"kumquat") + countif(rng,"lychee)

??
--ron

Is there a formula for searching for any one of a list of strings?
 

Try this:

=SUMPRODUCT(--(A1:A100={"apple","kumquat","pear"}))



"bookgirl" wrote:

I am making a template for a series of spreadsheets. I have a list of strings
and a series of spreadsheets which contain varying numbers (or none) of that
list.

E.g. In a series of monthly spreadsheets of crates of fruit bought by a
greengrocer, I want to search for particular types of fruit (apple, kumquat
etc.) to see if he bought any that month.

I can make a formula (COUNTIF...) display if one of them appears, but can't
find a way to count how many from my entire list appear (apple OR kumquat OR
lychee).

Please, can anybody help?

Is there a formula for searching for any one of a list of stri
 

Thank you all so much for your help. I have the spreadsheet working perfectly
now!
BookGirl

Is there a formula for searching for any one of a list of strings?
 

If pear appears twice, apple once and kumquat is not on the list, the
answer should be 2 as only two out of three fruits have appeared. Other
formulas give 3.


Ron Rosenfeld wrote:

On Fri, 1 Dec 2006 05:11:00 -0800, bookgirl
wrote:

I am making a template for a series of spreadsheets. I have a list of strings
and a series of spreadsheets which contain varying numbers (or none) of that
list.

E.g. In a series of monthly spreadsheets of crates of fruit bought by a
greengrocer, I want to search for particular types of fruit (apple, kumquat
etc.) to see if he bought any that month.

I can make a formula (COUNTIF...) display if one of them appears, but can't
find a way to count how many from my entire list appear (apple OR kumquat OR
lychee).

Please, can anybody help?

What about:

=countif(rng,"apple") + countif(rng,"kumquat") + countif(rng,"lychee)

??
--ron

Is there a formula for searching for any one of a list of strings?
 

On 1 Dec 2006 08:06:20 -0800, "Lori" wrote:

If pear appears twice, apple once and kumquat is not on the list, the
answer should be 2 as only two out of three fruits have appeared. Other
formulas give 3.

Ah! You're probably correct, although I initially read it differently.
--ron