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.

Try this one on for size:
=SUMPRODUCT(--(NOT(ISERROR(FIND("Japan",D3:D8,1)))))
where D3:D8 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.

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

"DeeW" wrote in message
...
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.

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

"Biff" wrote:

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

"DeeW" wrote in message
...
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.

You're welcome. Thanks for the feedback!

Biff

"DeeW" wrote in message
...
I followed your suggestions and they worked. Thanks for your help.

"Biff" wrote:

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

"DeeW" wrote in message
...
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.