+ Reply to Thread
Results 1 to 4 of 4

Count unique text only once

  1. #1
    Registered User
    Join Date
    02-15-2016
    Location
    London, UK
    MS-Off Ver
    14
    Posts
    32

    Count unique text only once

    I have a list of identification numbers and locations.
    I would like to count a number or text only once.
    In my example attached I can see Texas 4 times, California 3 times and Jersey 1 times. I would like to create formula that counts each location only once so my total is 3 ( as I have only 3 distinct location: Texas, California and Jersey).

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,263

    Re: Count unique text only once

    highlight A1:B9
    Click on Data -- Sort & Filter -- Advanced
    Click on Unique records only
    In the Copy to: box list where you would like results

    this will copy each individual unique item to the copy to range.
    You can then use Counta function to determine how many unique values you have.

  3. #3
    Registered User
    Join Date
    02-15-2016
    Location
    London, UK
    MS-Off Ver
    14
    Posts
    32

    Re: Count unique text only once

    I have found the formula that works: =SUMPRODUCT(1/COUNTIF(B2:B9,B2:B9))
    But I need to add a criteria which is another column with categories like P1, P2 etc. I need to see the unique entries for each category, can you help with updating the formula?

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,164

    Re: Count unique text only once

    You will need to use COUNTIFS.

    Then in G4 and filled across.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by FlameRetired; 04-19-2018 at 07:44 PM.
    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 17
    Last Post: 04-11-2016, 11:14 PM
  2. Replies: 2
    Last Post: 11-18-2014, 06:26 AM
  3. Import text file into Excel and using a loop to count unique words within the text
    By mrgriff21 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-24-2013, 03:17 PM
  4. Count Unique text against another unique text prior to todays date
    By VBSK8R in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-22-2011, 11:24 AM
  5. Replies: 17
    Last Post: 08-24-2009, 08:58 AM
  6. [SOLVED] Count unique text
    By shzdug in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-16-2006, 04:15 PM
  7. Unique count of text
    By Debbie in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-21-2005, 12:07 AM

Tags for this Thread

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