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