Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: count text in cells, dont count duplicates

  1. #1
    Board Regular
    Join Date
    Mar 2003
    Location
    OKC
    Posts
    114
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default count text in cells, dont count duplicates

    i need to count a column of text, but not count duplicates, tried this, but it doesnt work
    =SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

    Eddie
    John
    Eddie
    jack

    answer should be 3 total names

  2. #2
    MrExcel MVP Ron Coderre's Avatar
    Join Date
    Jan 2009
    Location
    Boston, Massachusetts
    Posts
    2,278
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count text in cells, dont count duplicates

    Try this:
    Code:
     
     
    =SUMPRODUCT(ISTEXT(A1:A100)*(A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))
    Is that something you can work with?

    Edited to include this comment:
    BTW....Your formula WILL count all unique items (text and numbers).
    If the count of your sample data is 4 and not 3...Are there possibly trailing spaces on any of them?
    Last edited by Ron Coderre; Jan 9th, 2010 at 12:45 PM.
    Best Regards,

    Ron Coderre
    Microsoft MVP-Excel (2006 - 2015)
    Using: Excel 2013 & 2016

  3. #3
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,480
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    4 Thread(s)

    Default Re: count text in cells, dont count duplicates

    Does the formula return the #DIV/0! error value? If so, then it's due to an inherent bug...

    http://groups.google.com/group/micro...0ec397f954a6ce

    Here are a couple of alternatives...

    =SUM(IF(A1:A100<>"",1/COUNTIF(A1:A100,A1:A100)))

    =SUM(IF(FREQUENCY(IF(A1:A100<>"",MATCH("~"&A1:A100,A1:A100&"",0)),ROW(A1:A100)-ROW(A1)+1),1))

    Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER. Also, the second formula is more efficient.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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