+ Reply to Thread
Results 1 to 4 of 4

Count unique values/text within a range of cells that contain duplicates, blanks & errors

  1. 06-25-2014, 02:29 AM #1
    kelvin.kuay
    kelvin.kuay is offline
    Registered User
    Join Date
    06-25-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    2013
    Posts
    2

    Post Count unique values/text within a range of cells that contain duplicates, blanks & errors

    Hi,

    Any ideas on how I can create a simple formula to count unique values/text within a range of cells that contain duplicates, blanks and errors?

    For e.g., in Column A (row 1 - 10):
    Proj-001
    Proj-001
    Proj-002

    Proj-004
    #N/A
    #N/A

    Proj-007
    Proj-002

    Thanks in advance!
    Reply With QuoteRegister To Reply
  2. 06-25-2014, 02:57 AM #2
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Count unique values/text within a range of cells that contain duplicates, blanks & err

    Hi,

    Assuming your blanks are "genuine" blanks (and not e.g. the "" as a result of formulas in those cells), and that, as appears in your example, there are no cells containing pure numericals in that range:

    =SUMPRODUCT(ISTEXT(A1:A10)/COUNTIF(A1:A10,A1:A10&""))

    Otherwise, array formula**:

    =SUM(IF(FREQUENCY(IF(1-ISNA(A1:A10),IF(A1:A10<>"",MATCH(A1:A10,A1:A10,0))),ROW(A1:A10)-MIN(ROW(A1:A10))+1),1))

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  3. 06-25-2014, 03:21 AM #3
    kelvin.kuay
    kelvin.kuay is offline
    Registered User
    Join Date
    06-25-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    2013
    Posts
    2

    Re: Count unique values/text within a range of cells that contain duplicates, blanks & err

    Worked perfectly. Thank you very much!
    Reply With QuoteRegister To Reply
  4. 06-25-2014, 03:28 AM #4
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Count unique values/text within a range of cells that contain duplicates, blanks & err

    You're welcome!
    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. [SOLVED] Find unique values from range ( populated cells only) & not count zeros or NA errors VBA
    By KK1234 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-12-2014, 06:50 AM
  2. Count Unique Text & blanks in one column prior to Today's date
    By VBSK8R in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-21-2011, 01:25 PM
  3. Count unique text values in range with criteria
    By cdiaz in forum Excel General
    Replies: 3
    Last Post: 07-15-2011, 10:43 AM
  4. [SOLVED] Count Duplicates in a range - skip blanks
    By jhahes in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-08-2006, 04:50 PM
  5. [SOLVED] Count unique values among duplicates in a subtotal range
    By jcpotwor in forum Excel General
    Replies: 2
    Last Post: 01-12-2006, 09:35 AM

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