Excel 2000 - Formulas and Functions
 

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.

Excel 2000 - Formulas and Functions
 

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.

Excel 2000 - Formulas and Functions
 

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.

Excel 2000 - Formulas and Functions
 

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.

Excel 2000 - Formulas and Functions
 

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.