+ Reply to Thread
Results 1 to 7 of 7

Count the number of Unique Items in a Column for each Unique Item in another Column

  1. #1
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Count the number of Unique Items in a Column for each Unique Item in another Column

    Is there any way to do a count of the number of unique items in Column B for each unique item in column A without using a helper column and a Pivot Table.

    I'm using this formula in a helper column follwed by a pivot table to give me the results. I'm trying to achieve the same result without either the helper column or the pivot table in the attached sample.

    Please Login or Register  to view this content.
    Many thanks
    Attached Files Attached Files
    Thanks,
    HangMan

    You can say "Thank you!" by clicking Add Reputation below the post.
    Please, mark your thread [SOLVED] if you are happy with the solution.

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Count the number of Unique Items in a Column for each Unique Item in another Column

    Enter formula in F12 and copy down

    =SUMPRODUCT(($A$2:$A$27=E12)/COUNTIFS($A$2:$A$27,$A$2:$A$27,$B$2:$B$27,$B$2:$B$27&""))
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Count the number of Unique Items in a Column for each Unique Item in another Column

    That was quick... works perfectly many thanks.

    Is the &"" to allow for any blanks and prevent a #DIV/0! error?

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Count the number of Unique Items in a Column for each Unique Item in another Column

    Quote Originally Posted by HangMan View Post
    That was quick... works perfectly many thanks.

    Is the &"" to allow for any blanks and prevent a #DIV/0! error?
    Yes, it is.

    Please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

  5. #5
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Count the number of Unique Items in a Column for each Unique Item in another Column

    Whilst it does the job perfectly, it is incredibly slow to calculate when dealing with around 5,000 rows, is that what you would expect?

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Count the number of Unique Items in a Column for each Unique Item in another Column

    Quote Originally Posted by HangMan View Post
    Whilst it does the job perfectly, it is incredibly slow to calculate when dealing with around 5,000 rows, is that what you would expect?
    Try this array formula. It should be better speed-vise than SUMPRODUCT

    =SUM(IF(FREQUENCY(IF(A$2:A$27=E12,MATCH(B$2:B$27,B$2:B$27,0)),ROW(B$2:B$27)-MIN(ROW(B$2:B$27))+1),1))

    ***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

  7. #7
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Count the number of Unique Items in a Column for each Unique Item in another Column

    Ah, I didn't even have time to make a cup of coffee running this one

    Much quicker, many thanks for the quick reply as well...

+ 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: 4
    Last Post: 10-09-2015, 05:19 AM
  2. [SOLVED] How can I count unique items in a filtered column?
    By terry_believers in forum Excel General
    Replies: 11
    Last Post: 07-12-2012, 09:52 AM
  3. counting number of unique items in column
    By maacmaac in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-10-2008, 02:17 PM
  4. Count unique items in column.
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-19-2008, 10:14 AM
  5. [SOLVED] How do I set up a formula to count only unique items in a column?
    By LYLERR in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-26-2006, 07:10 PM
  6. RE: How do I set up a formula to count only unique items in a column?
    By jennifer in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-26-2006, 07:10 PM
  7. Count number of unique items in a column that contains duplicates
    By Steembeem in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-01-2005, 09:06 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1