Thanks:  0
Likes:  0

# Thread: Counting the number of different values in a list

1. Here's the problem:
I have a long list of countries, many of which occur several times. I want a formula which will determine how many DIFFERENT coutries there are in the list.

E.g:

China
Denmark
Denmark
Finland

Total = 3

It feels like it should be simple - but I can't do it! Thanks in advance...

2. ok. this is a long away..
just incase it is urgent..its all i know off my head.

select your fiels. then go to data/filter/advanced filter.. and filter by unique fields.
then copy these fields to a new column and see how many rows there are.

3. On 2002-04-09 00:18, Saloomi wrote:
Here's the problem:
I have a long list of countries, many of which occur several times. I want a formula which will determine how many DIFFERENT coutries there are in the list.

E.g:

China
Denmark
Denmark
Finland

Total = 3

It feels like it should be simple - but I can't do it! Thanks in advance...
Array-enter:

=SUM(IF(LEN(A2:A5),1/COUNTIF(A2:A5,A2:A5)))

where A2:A5 houses the country data.

In order to array-enter a formula you need to hit control+shift+enter, not just enter.

htats what i sort of meant...

5. Hi Saloomi

I would seriously consider a small Pivot Table instead.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•