+ Reply to Thread
Results 1 to 8 of 8

Counting unique values (text or number) in one column

  1. #1
    Registered User
    Join Date
    12-04-2012
    Location
    At my desk
    MS-Off Ver
    2010
    Posts
    9

    Counting unique values (text or number) in one column

    I apologize upfront as this is probably a very simple thing. I'm either over thinking it or just not getting something.
    (it'll probably take one of you a second or 2 to figure out)
    I did the search and reviewed a lot of suggestions / solutions - some of which were more complex as some were looking for unique values based on some other criteria.

    What I have is a list that continues to grow (it could be a list of 1,000 today and 2,000 in 6 months). I'd like to be able to have a formula at the top of a column that outputs the unique count of instances in that column.

    Please refer to the attached example...
    Thanks in advance!
    CountUniqueValues-1Column.xlsx

  2. #2
    Valued Forum Contributor
    Join Date
    08-14-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    573

    Re: Counting unique values (text or number) in one column

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    confirmed with ctrl+Shift+enter. You can change A41 to any cell in A column

  3. #3
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Counting unique values (text or number) in one column

    =SUMPRODUCT(1/COUNTIF(A4:A31,A4:A31))

    Normally Enter

  4. #4
    Valued Forum Contributor
    Join Date
    08-14-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    573

    Re: Counting unique values (text or number) in one column

    But it is mentioned the range is unknown. It may grow. This formula will return div#0 error if blank cell is there in the range.
    Quote Originally Posted by Teethless mama View Post
    =SUMPRODUCT(1/COUNTIF(A4:A31,A4:A31))

    Normally Enter

  5. #5
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Counting unique values (text or number) in one column

    Quote Originally Posted by Sindhus View Post
    But it is mentioned the range is unknown. It may grow. This formula will return div#0 error if blank cell is there in the range.
    OK, No problem.

    =SUMPRODUCT((A4:A31<>"")/COUNTIF(A4:A31,A4:A31&""))

    Still normally enter

  6. #6
    Valued Forum Contributor
    Join Date
    08-14-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    573

    Re: Counting unique values (text or number) in one column

    @Teethless mama Great I gave this a try before. But couldnt get it. That last &"" is simply awesome

  7. #7
    Registered User
    Join Date
    12-04-2012
    Location
    At my desk
    MS-Off Ver
    2010
    Posts
    9

    Re: Counting unique values (text or number) in one column

    AGREED! Thank you both for your valuable input! A special thanks to Teethless mama for a simple solution!

  8. #8
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Counting unique values (text or number) in one column

    You're Welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1