Counting the number of different values in a list

Saloomi · Apr 9th, 2002, 01:18 AM

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...

Qroozn · Apr 9th, 2002, 01:40 AM

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.

Aladin Akyurek · Apr 9th, 2002, 01:45 AM

Quote:

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.

Qroozn · Apr 9th, 2002, 01:46 AM

ALADIN...
htats what i sort of meant...

Dave Hawley · Apr 9th, 2002, 03:45 AM

Hi Saloomi

I would seriously consider a small Pivot Table instead.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 9th, 2002, 01:18 AM	#1
Saloomi New Member Join Date: Apr 2002 Posts: 10	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...

Apr 9th, 2002, 01:40 AM	#2
Qroozn Board Regular Join Date: Mar 2002 Location: Sydney/Brisbane , Australia Posts: 539	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.

Apr 9th, 2002, 01:46 AM	#4
Qroozn Board Regular Join Date: Mar 2002 Location: Sydney/Brisbane , Australia Posts: 539	ALADIN... htats what i sort of meant...

Apr 9th, 2002, 03:45 AM	#5
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Saloomi I would seriously consider a small Pivot Table instead.