+ Reply to Thread
Results 1 to 4 of 4

Formula for counting unique text values in a single column

  1. 03-08-2019, 10:12 AM #1
    radar1066
    radar1066 is offline
    Registered User
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    MSO 2016
    Posts
    1

    Formula for counting unique text values in a single column

    Hi, we have a column with hundreds of serial numbers in them. These are all 2 letters followed by 6 numbers. I want to run a formula to count the unique entries, i.e. only count a unique serial number once even if it appears many times (which they do). I know I can use excel manually to remove duplicates etc but I wanted to know if there was a formula that could do this. I'm not looking to create an table to count each instance just a formula that will tell me how many unique entries are in the column. Any help would be appreciated:



    Serial Number
    TN117748
    TN711014
    TN580273
    TN718071
    TN730239
    TN762657
    TN117748
    TN711014
    TN117748
    TN711014
    TN392604
    TN534813
    TN711002
    TI774283
    TN117748
    TN711014
    TN782538
    TN117748
    TN711014
    TN117748
    TN711014
    TN117748
    TN711014
    TN117748
    TN711014
    Reply With QuoteRegister To Reply
  2. 03-08-2019, 10:30 AM #2
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: Formula for counting unique text values in a single column

    Hi radar and welcome to the forum,

    Find the attached showing 11 unique number in your list. I hope you can adapt it to your problem.

    Count Unique after some helper.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  3. 03-08-2019, 10:31 AM #3
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Formula for counting unique text values in a single column

    you could try this, adjust the range as needed...
    =SUMPRODUCT((A2:A26<>"")/COUNTIF(A2:A26,A2:A26&""))
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  4. 03-08-2019, 10:33 AM #4
    CK76
    CK76 is offline
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,887

    Re: Formula for counting unique text values in a single column

    There are many ways to do this.
    One such example is FREQUENCY function.
    https://support.office.com/en-us/art...0-a929fbc1e273

    Other function typically used is...
    =SUMPRODUCT(1/COUNTIF(A2:A26,A2:A26))
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula for counting values in one column based off of unique values in a different column
    By vha7 in forum Excel General
    Replies: 7
    Last Post: 10-23-2018, 10:18 AM
  2. Find unique values in column - in a SINGLE CELL FORMULA; can it be done please?
    By iantix in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-25-2017, 12:01 PM
  3. [SOLVED] Unique values to a single column -- formula constraints
    By macrorookie in forum Excel General
    Replies: 1
    Last Post: 09-01-2016, 02:40 AM
  4. Extracting unique values from a list in multiple columns into a single column (by formula)
    By Mukun in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-16-2016, 08:09 PM
  5. Counting Unique Values of a column if another column is = "Specific Text"
    By dansilver in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-08-2016, 05:10 PM
  6. [SOLVED] counting unique values in col A against unique value in column B
    By greyscale in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2013, 08:43 AM
  7. [SOLVED] Counting unique values (text or number) in one column
    By djaurit in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-06-2012, 03:13 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1