+ Reply to Thread
Results 1 to 12 of 12

Count unique values in one column given multiple criteria for other columns

  1. #1
    Registered User
    Join Date
    04-21-2011
    Location
    Kentucky, United States
    MS-Off Ver
    Excel 2007
    Posts
    10

    Question Count unique values in one column given multiple criteria for other columns

    I looked through the forum, and couldn't find anything exactly like my problem.

    I cannot use VBA.

    My problem is this:

    I have a spreadsheet with multiple columns: Column A, B, C, D, etc. I need to know the sum of UNIQUE values in column A where certain conditions are met in Columns C, D, E, etc.

    For one row, I expect the forumula would look something like the following:

    If (Column B == "crit1"){
    if (Column C == "crit2"){
    if (Column D != "crit3"){
    count_unique_values(Column A);
    }
    }
    }

    Of course, nothing like this will work since my sheet has multiple rows with a mix of values.

    Note that only UNIQUE values in column A shall be counted.

    How could I expand the equation to include additional criteria in other columns?

    Thanks in advance! Please let me know if there are any questions.
    Last edited by redraishes; 06-09-2011 at 03:19 PM.

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Count unique values in one column given multiple criteria for other columns

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!

  3. #3
    Registered User
    Join Date
    04-21-2011
    Location
    Kentucky, United States
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Count unique values in one column given multiple criteria for other columns

    Attached is a dummy workbook of what I am trying to achieve. For the sake of example, let's say that I am trying to count unique values in Column A where values in Column B = "g", C = "3", and D != "y".

    Also, blank values in Column A should be ignored.

    As shown in highlighting within the workbook, three values meet the criteria: "1", "cat", and "cat". However, the function needs to return "2" since that is the sum of UNIQUE values.

    I hope this clarifies things.

    Thanks.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor scottylad2's Avatar
    Join Date
    09-03-2010
    Location
    edinburgh
    MS-Off Ver
    Office 2007 Prof & Office 2010 Student Edition
    Posts
    629

    Re: Count unique values in one column given multiple criteria for other columns

    I can't see how you could do it without introducing a helper column. My attempt would be to list the items that meet your criteria in a column, I've used column F which has an array formula to pull through all values that meet the criteria, then in column G a count of the unique. Is this close to what you need? I had already made up my sample before you posted, i'm sure you can adapt to meet your layout. If it's just the count you need then you can hide column F

    ps the Array Formula needs to be entered with Ctrl + Shift + Enter, not just enter
    Attached Files Attached Files
    Windows 7 using Office 2007 & 2010

    Remember your [ code ] [ /code ] tags, makes reading soooo much easier

  5. #5
    Valued Forum Contributor scottylad2's Avatar
    Join Date
    09-03-2010
    Location
    edinburgh
    MS-Off Ver
    Office 2007 Prof & Office 2010 Student Edition
    Posts
    629

    Re: Count unique values in one column given multiple criteria for other columns

    I can't see how you could do it without introducing a helper column. My attempt would be to list the items that meet your criteria in a column, I've used column F which has an array formula to pull through all values that meet the criteria, then in column G a count of the unique. Is this close to what you need? I had already made up my sample before you posted, i'm sure you can adapt to meet your layout. If it's just the count you need then you can hide column F

    I've added some CF to make it easier to look at
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-21-2011
    Location
    Kentucky, United States
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Count unique values in one column given multiple criteria for other columns

    Scotty,

    Thanks for the reply. That is exactly what I want, except without the helper column.

    I had a similar request a bit back, here is the URL: http://www.excelforum.com/excel-gene...e-columns.html

    Similarly to now, I was looking for a way to count unique entities in one column based on requirements being met in other columns. The difference is that I now need the function to check an additional column for values NOT equal to some criterion.

    What you did is exactly what I asked for. I apologize for not specifying that I cannot use a helper column.

    Thanks.

  7. #7
    Valued Forum Contributor scottylad2's Avatar
    Join Date
    09-03-2010
    Location
    edinburgh
    MS-Off Ver
    Office 2007 Prof & Office 2010 Student Edition
    Posts
    629

    Re: Count unique values in one column given multiple criteria for other columns

    ok no problem. For what it's worth I don't think you could do this in a non VBA scenario without a helper column, trying to incorporate an array formula to extract the criteria matching records and then count the unique instances of items from within the results is bound to throw up a circular reference.

    But there's some pretty smart people on here who continue to prove there's more than one way to crack a nut

    good luck

  8. #8
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Count unique values in one column given multiple criteria for other columns

    See the attached.
    Attached Files Attached Files
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  9. #9
    Registered User
    Join Date
    04-21-2011
    Location
    Kentucky, United States
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Count unique values in one column given multiple criteria for other columns

    Haseeb,

    Thank you for your work. This is very close to what I need.

    How difficult would it be to change the equation to, rather than select rows in column D that contain the value given in cell I3, instead select all rows EXCEPT those that contain I3? In other words, if I wanted to select all unique values in column A where values in B and C match specified values, and values in D include all values except the specified value.

    Also, would it be possible to make the inputs of the function include ranges rather than single cells? For example, if I wanted to select rows where the values in column B are "4", "g", or "d", instead of only "4"?

    Once again, thank VERY much you for your work. Your help is greatly appreciated.

  10. #10
    Registered User
    Join Date
    04-21-2011
    Location
    Kentucky, United States
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Count unique values in one column given multiple criteria for other columns

    *bump*

    Anybody?

  11. #11
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Count unique values in one column given multiple criteria for other columns

    See Sheet2 in attached.

    Does this help?
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    04-21-2011
    Location
    Kentucky, United States
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Count unique values in one column given multiple criteria for other columns

    Haseeb,

    This works perfectly and is EXACTLY what I needed! Thanks so much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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