Excel 2000 - Formulas and Functions

[Guest]

I have a table that was created in Word 2000 that has been copied into Excel
2000. There is a column (A) that contains a list of countries and I would
like to count the number of times each country appears in column A. In Column
B I typed =countif(a1:a20,"Japan") and the value is returned.

The problem is that some cells in column A contain more than one country,
for example Japan, Italy. If I use 'countif' it returns the value as zero. Is
there another formula I can use?

The desired results is to determine how many times the country is repeated
and then to generate a chart based on the results. This is all text and other
than the 'tally' there are no numbers involved.

Any help would be appreciated.

 

[slingsh0t]

Try this one on for size:
=SUMPRODUCT(--(NOT(ISERROR(FIND("Japan",D38,1)))))
where D38 contains the list of countries. This works when the
countries are listed once per cell, as in your example "Japan, Italy".
"Japan, Italy, Japan" would yield only 1 in the count of Japan.

 

[Biff]

Try one of these if "Japan" may only appear once per cell:

=COUNTIF(A1:A20,"*Japan*")

=SUMPRODUCT(--(ISNUMBER(SEARCH("Japan",A1:A20))))

If "Japan" may appear more than once per cell:

=SUMPRODUCT((LEN(A1:A20)-LEN(SUBSTITUTE(UPPER(A1:A20),"JAPAN",""))))/LEN("Japan")

Biff

 

[Guest]

I followed your suggestions and they worked. Thanks for your help.

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

I followed your suggestions and they worked. Thanks for your help.