+ Reply to Thread
Results 1 to 3 of 3

Need unique count of text from multiple columns by category

  1. #1
    Registered User
    Join Date
    06-30-2011
    Location
    Livermore, CA, US
    MS-Off Ver
    Excel 2010
    Posts
    3

    Unhappy Need unique count of text from multiple columns by category

    Hi,

    I need your help.

    My data looks something like (assume begins in A1):
    Category,Label1,Label2
    Dogs,blank,doe
    Dogs,zoo,blank
    Cats,zoo,yup
    Cats,doe,hgg
    Cats,blank,doe

    What the result should look like:
    Dogs, 2
    Cats, 4

    My actual data has 100s of rows and 5 label columns.

    I'm looking for a solution that either uses sumproduct or an array formula and can handle blanks. I don't want to create a UDF in VBA.

    The closest I got is with this formula which got me .83: SUM((($A$2:$A$6="Dogs")*($B$2:$C$6<>""))/(COUNTIF($B$2:$C$6,$B$2:$C$6&"")))

    I've spent the better part of the day researching this and trying to solve this. I've found solutions that were close to what I was looking for but was not able to successfully modify them.

    Looking forward to your help.
    Thanks
    Dan

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    25,329

    Re: Need unique count of text from multiple columns by category

    Why should Cats be 4?? Doesn't this give you the result that you need?

    =COUNTIF(A1:C5,"Dogs")
    Glenn



  3. #3
    Registered User
    Join Date
    06-30-2011
    Location
    Livermore, CA, US
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Need unique count of text from multiple columns by category

    Because I am looking for the unique count of labels by category.

    In this case, between both "label" columns, Cats has a total of 4 unique labels: zoe, yup, hgg, doe

+ 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. [SOLVED] Count the number of unique items (multiple columns)
    By HJHamm in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-20-2015, 10:41 AM
  2. [SOLVED] Sort and Count Unique List Across Multiple Columns - Student Count
    By BuntyMac in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 08-17-2014, 07:00 AM
  3. To count multiple unique text as 1 from 5 cloumns
    By malitec in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-19-2014, 01:36 AM
  4. [SOLVED] Count unique data across multiple columns
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-29-2013, 06:45 PM
  5. Count unique values across 2 columns with multiple criteria
    By Skywalker01 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-29-2012, 08:04 AM
  6. Replies: 4
    Last Post: 07-20-2011, 09:20 AM
  7. Replies: 11
    Last Post: 06-09-2011, 03:17 PM

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