 Thanks:  0 Likes:  0

# Thread: Counting Unique Records

1. ## 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.  Reply With Quote

2. ## =COUNT(IF(FREQUENCY(A:A,A:A),1))

...where your values are listed in column A.  Reply With Quote

3. ## 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.  Reply With Quote

4. ## 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.  Reply With Quote

5. ## 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?

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.
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 ]  Reply With Quote

6. ## 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]  Reply With Quote

## 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
•