Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Chicago
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Array formula (xp)

    Hi All

    I have a list of names in one column of a worksheet. At the bottom, I'd like to count the number of unique names I have. I've used (I thought) the following formula before but I just typed it in and it's not working.
    The formula is {=SUM(1/COUNTIF(range,same range))}. I use the CTRL-SHIFT-ENTER key combination to get the curly brackets.

    Thanks

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

    Re: Array formula (xp)

    Hi Nancy

    That formula will fail if there are any blank cells.
    You could use this array formula
    <code>{=SUM(IF(COUNTIF(Range,Range)=0,"",1/COUNTIF(Range,Range)))}</code>

    For some alternative formulas see Count Unique Elements in a Cell Range in Excel

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Chicago
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array formula (xp)

    Thank you! Thank you!
    That was exactly the problem.

    Have a great day and thanks again.

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

    Re: Array formula (xp)

    Non array formula,

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

    Using Morefunc addin

    =COUNTDIFF(A1:A13)

Posting Permissions

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