Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    count no duplicate value (2000)

    i have in the column A5:A100 this value:
    1
    1
    1
    2
    2
    2
    3
    3
    3
    4
    4
    4
    5
    5
    5
    6
    6
    6
    9
    9
    9
    52
    52
    23
    23
    23
    23
    ...

    my problem is to count (with a formula) the value not duplicate and insert the result in D2
    In this case D2=9

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: count no duplicate value (2000)

    Provided there are no blanks and no text cells:

    =SUM(N(FREQUENCY(A1:A100,A1:A100)>0))

    See Chip Persons' site Duplicates and Unique Values page for more info.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: count no duplicate value (2000)

    You can use an array formula, with your range in A5:A100, you need to enter the formula by using Control+Shift+Enter
    There must be no blanks for this to work.

    =SUM(1/COUNTIF(A5:A100,A5:A100))

    =SOMMA(1/CONTA.SE(A5:A100;A5:A100))

  4. #4
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count no duplicate value (2000)

    With this add-in,

    http://longre.free.fr/english/index.html

    =COUNTDIFF(RANGE)

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count no duplicate value (2000)

    This will handle blanks, either by omission and/or formula generated blanks,

    =SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

Posting Permissions

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