Counting Unique Records

[ksmithcpa]

I need a formula to count the number of unique records in a column. For example:

100
200
300
100
400
100

This column contains 4 unique records. Any ideas are appriciated.

[Mark W.]

=COUNT(IF(FREQUENCY(A:A,A:A),1))

...where your values are listed in column A.

[Aladin Akyurek]

On 2002-04-22 08:07, ksmithcpa wrote:
I need a formula to count the number of unique records in a column. For example:

100
200
300
100
400
100

This column contains 4 unique records. Any ideas are appriciated.

Array-enter:

=IF(LEN(A2:A50),1/COUNTIF(A2:A50,A2:A50))

where A2:A50 is the range of interest.

In order array-enter a formula, hit control+shift+enter at the same time, not just enter.

[ksmithcpa]

When I try this formula with my original example I get 9, when what I want is 4. I did have to modify the formula a little to get excel to accept it.

=count(if(frequency(A:A,A:A),1,0)

Am I missing something?

On 2002-04-22 08:09, Mark W. wrote:
=COUNT(IF(FREQUENCY(A:A,A:A),1))

...where your values are listed in column A.

[Mark W.]

On 2002-04-22 11:27, ksmithcpa wrote:
When I try this formula with my original example I get 9, when what I want is 4. I did have to modify the formula a little to get excel to accept it.

=count(if(frequency(A:A,A:A),1,0)

Am I missing something?

Your modification changed the functioning of the formula. Use...

=COUNT(IF(FREQUENCY(A:A,A:A),1))

...where your values, {100;200;300;100;400;100}, are the ONLY contents of column A. If needed substitute an cell range (e.g., A1:A6) for the column references, A:A, but DO NOT specify a 3rd argument for the IF worksheet function. Without the 3rd argument IF produces FALSE which isn't counted by the COUNT worksheet function.

[ This Message was edited by: Mark W. on 2002-04-22 11:36 ]

[ksmithcpa]

It worked!! Thank you very MUCH!!!

This was my first time using this board and I got a very quick response. Going to add it to my list of favorites.

Thanks again.

Ken

[/quote]

Your modification changed the functioning of the formula. Use...

=COUNT(IF(FREQUENCY(A:A,A:A),1))

...where your values, {100;200;300;100;400;100}, are the ONLY contents of column A. If needed substitute an cell range (e.g., A1:A6) for the column references, A:A, but DO NOT specify a 3rd argument for the IF worksheet function. Without the 3rd argument IF produces FALSE which isn't counted by the COUNT worksheet function.

[ This Message was edited by: Mark W. on 2002-04-22 11:36 ]
[/quote]