+ Reply to Thread
Results 1 to 9 of 9

Excel 2007 : Count unique vales w/ multiple criteria

  1. #1
    Registered User
    Join Date
    01-15-2010
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2007
    Posts
    6

    Count unique vales w/ multiple criteria

    Hi All,

    I'm having an issue trying to find a formula for counting unique values with multiple criteria. So, I'm looking to count the number of unique Cases for Staff Member where Status is Established.

    So for GV, the magic number would 3. For JH, it would be 4.
    Staff Member Case Number Status
    GV 391818 Established
    GV 391819 Established
    GV 391820 Established
    GV 391821 Denied
    GV 391818 Established
    GV 391819 Established
    JH 391839 Established
    JH 391830 Established
    JH 391838 Denied
    JH 391838 Established
    JH 391834 Established


    For the users (not me) sake, I think NOT an array formula would be best. But I'll take what I can get.

    Thank you very much.
    Last edited by jhendrix; 01-21-2010 at 09:21 PM.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,264

    Re: Possible? Count of unique vales w/multiple criteria

    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
    01-15-2010
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Possible? Count of unique vales w/multiple criteria

    Thank you for responding so fast!

    Attached is a sample file with many more rows.
    Attached Files Attached Files

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Possible? Count of unique vales w/multiple criteria

    Hi,

    sort by Case number, then add a helper column D with

    D2 =IF(B2=B1,D1+1,1) and copy down

    then you can use

    =COUNTIFS(A:A,"=GV",C:C,"= Established",D:D,"=1")

    Note that your column C has leading spaces for the word "Established", but none for "Denied"

    hth

  5. #5
    Registered User
    Join Date
    01-15-2010
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Possible? Count of unique vales w/multiple criteria

    Thank you for replying.

    So I simplified/modified the actual file before uploading it. In it's actual use, someone would be taking this canned BO report, with many more columns in between these, then copy and pasting it into an excel spreadsheet, where another sheet will do all the calculations, which will then be pulled into a dashboard.

    So sorting wouldn't be ideal, as the people I'm trying to develop this for will have even less excel skills than I. And If I can get around adding another column, I would prefer to.

  6. #6
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Possible? Count of unique vales w/multiple criteria

    OK, try this on your pianola:

    you need to create a table where you list your staff members and calculate the number of unique established in the adjacent cell.

    Please Login or Register  to view this content.
    Staff member is column F, and the following formula sits in column G

    Please Login or Register  to view this content.
    Note that I'm using TRIM() in the condition that checks the status, to get rid of leading and/or trailing spaces.

    try the attached file
    Last edited by teylyn; 01-22-2010 at 01:11 AM.

  7. #7
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Possible? Count of unique vales w/multiple criteria

    This one's an alternative formula for column G, and although the formula is a bit shorter and not as unwieldy, it takes slightly longer to calculate

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    01-15-2010
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Possible? Count of unique vales w/multiple criteria

    Thank you both! They both work.

    The problem I'm facing now, is that these calculations would be happening on a different sheet, and I can't seem to correctly reference everything.

    I think I can follow along with most the formula, but I get lost somewhere.

    I've attached a more accurate version of the file. I've deleted all the irrelevant column data. Also what I forgot to mention, is that I want to count if the Status is Established or Partially Established. Only Denied cases would be neglected.

    Thank you all very much. This is a great learning experience.
    Attached Files Attached Files
    Last edited by jhendrix; 01-22-2010 at 10:57 AM.

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Count unique vales w/ multiple criteria

    Quote Originally Posted by jhendrix
    ...If I can get around adding another column, I would prefer to.
    you can, but as outlined already it makes the process more "expensive" ...

    generally speaking utilising helpers mean more efficient formulae.... in this case you'd even be able to use a Pivot Table.

    You can create a named range:

    Please Login or Register  to view this content.
    In the Named range I'm assuming A only ever contains text values.

    Then

    Please Login or Register  to view this content.
    The above works given the 1:1 relationship between Staff/Case/Status
    (ie a Case only belongs to 1 Staff member (seemingly) and has only 1 status (seemingly))

    Insert a PT with Range defined as _Data, subsequently setting Staff as Row Label and Unique as Data Field (set to SUM)

    Sorting the data as Teylyn has already said would really aid efficiency in terms of the above process given you could dispense with COUNTIF - but I've disregarded given you've already made mention this is not viable.

    In terms of conducting the calcs without the use of the additional column whilst still utilising the Named Range then again taking advantage of the 1:1 relationships:

    Please Login or Register  to view this content.
    but the above will perform poorly...

    As a final option - if you were happy to keep the helper column but did not want to use a Pivot Table you could just use a SUMIF in place of the above which would be significantly quicker:

    Please Login or Register  to view this content.
    EDIT: the above SUMIF is in essence just a reworking of Teylyn's earlier point re: use of COUNTIFS but working off later assertion that data can not be sorted...

    If adopting the helper but no PT approach you could still use COUNTIFS and replace the COUNTIF calc in the helper col. with a a basic MATCH approach which might be quicker pending volume ?

    Please Login or Register  to view this content.
    Last edited by DonkeyOte; 01-22-2010 at 12:04 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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