Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Counting Unique Records

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

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

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,993
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    6 Thread(s)

    Default

    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.

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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