+ Reply to Thread
Results 1 to 12 of 12

Count Unique Items in a Column Based on Unique Items in Two Other Columns

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

    Count Unique Items in a Column Based on Unique Items in Two Other Columns

    I'm using this array formula to count the number of Unique Items in Column B for each Unique Item in Column A

    Please Login or Register  to view this content.
    I need to expand this formula so that it now counts the number of unique Colours in column C for each unique Animal in column B with a unique letter in column A. So when the entries are changed in cells J14 and J15 it gives the answer in cell J16. Please see sample attached.

    Many thanks
    Last edited by HangMan; 10-31-2015 at 07:25 AM.
    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 Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,335

    Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

    Seems to me this is far simpler with a Pivot table and avoids all the messy stuff associated with complex formulae - see attached
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

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

    Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

    Hi Richard,

    It would be, but I can't use a Pivot Table for what I need to achieve which is why I'm tying to find a formulaic solution.

    Many thanks

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

    Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

    But if I could use a Pivot Table to identify the count then the next problem I have is then being able to populate different cells with the results, so for example, based on the pivot table we know there are 3 colours for Cat with the Letter B, what I then need to be able to do is for specific cells to be populated with those colours, e.g. F4 with the first colour, F5 with the second colour and F6 with the third colour and so on, bearing in mind there could be any number of unique colours that meet the criteria.

    Many thanks

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

    Not sure what results you expect.

    See if this does what you want.

    Array entered**:

    =SUM(IF(FREQUENCY(IF((A2:A27=J14)*(B2:B27=J15),MATCH(C2:C27,C2:C27,0)),ROW(C2:C27)-ROW(C2)+1),1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

    Try this array formula.

    =SUM(IFERROR(1/COUNTIFS(A2:A27,J14,B2:B27,J15,Colour,Colour),0))

    Although I think it only works because all of the colours are unique, if you have the same colour for several animals I think results could be wrong.
    Last edited by jason.b75; 10-31-2015 at 08:04 AM.

  7. #7
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,335

    Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

    Hi,

    The point about a pivot table is that the view of the data can be simply changed by dragging and dropping field names around. If you need colours reported in the table for a particular letter and animal then move those two fields into the Report Filter area of the PT - see attached for an example.

    OK the colours may not be in F4, F5 & F6 but they are certainly in F5, F6 & F7 with the total of 3 in F8. But if you just want the total 3 then just drag the colours field into the Report Filter area.

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

    Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

    Hi Richard,

    I understand what you are saying and I think you are right, I can probably achieve what I need by adding the pivot to a hidden sheet and pulling the values from there.

    Many thanks for your help, I think I was trying to over complicte this, though I'm curious to see if it can be achieved with a formula alone but for now I'll adopt the PT route I think.

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

    Quote Originally Posted by HangMan View Post
    I'm curious to see if it can be achieved with a formula alone
    Did you try either of the formulas that were suggested?

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

    Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

    Hi Tony and Jason,

    Apologies, sometimes the forum behaves strangely, I didn't receive any notification of either of your repies, only Richards, so subsequently I didn't see either of your replies until now.

    Many thanks, both solutions work perfectly and overcome my issue with not wanting to use a pivot table...

    Very much appreciated, thank you...

  11. #11
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

    Good deal. Thanks for the feedback!

  12. #12
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Count Unique Items in a Column Based on Unique Items in Two Other Columns

    If you're referring to email notifications then you only get notified of the first new post since your last visit.

    On the forum, User CP, you only see the latest post at the time you refreshed the page, and it will only be shown in bold if the post was submitted after you last viewed the thread.

    It is down to you to check if you have had one reply, or several.

+ 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 in a Column for each Unique Item in another Column
    By HangMan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-27-2015, 01:18 PM
  2. [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
  3. Return Unique Items based on Count ans descending
    By jhall488 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-12-2014, 04:30 PM
  4. [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
  5. Count unique items in column.
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-19-2008, 10:14 AM
  6. 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
  7. [SOLVED] 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

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