+ Reply to Thread
Results 1 to 6 of 6

Countif Unique Records in List Match Criteria

  1. #1
    Registered User
    Join Date
    05-09-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    38

    Countif Unique Records in List Match Criteria

    I'm trying to figure out a formula for this one. From reading other posts I know I'll need a combination of the Countif formula, Frequency formula, Match formula, and Row formula. I'm just not sure how to put them together.

    Example: I'm trying to write a formula that will tell me how many days (therefore unique records) each individual worked. Let's focus just on Aaron. The result for Aaron should return the value of 4, because there were only 4 unique records tied to his name. Therefore Charlie = 1 day, and Doug = 3 days. How can I write this formula for Aaron? (I can figure out the rest if I can get the first one started).

    Thanks!

    A B
    1 Associate Date
    2 Aaron 6/16/12
    3 Aaron 6/16/12
    4 Aaron 6/16/12
    5 Aaron 6/17/12
    6 Aaron 6/17/12
    7 Aaron 6/18/12
    8 Aaron 6/19/12
    9 Charlie 6/12/12
    10 Charlie 6/12/12
    11 Charlie 6/12/12
    12 Doug 6/27/12
    13 Doug 6/28/12
    14 Doug 6/28/12
    15 Doug 6/29/12

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

    Re: Countif Unique Records in List Match Criteria

    Array confirmed with Shift Ctrl Enter

    =SUM(IFERROR(1/COUNTIFS($A$2:$A$15,"Aaron",$B$2:$B$15,$B$2:$B$15),0))

    "Aaron" could be entered in another cell so that you can copy the formula instead of editing for the others.

  3. #3
    Registered User
    Join Date
    05-09-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: Countif Unique Records in List Match Criteria

    Thanks for your help jason.b75. For some reason I'm getting a few of my results with decimals (i.e. 12.2 or 7.2). My database is about 4000 rows long. I'm getting good results (whole numbers) with some of the names, but not all of them for some reason. Thoughts?
    Last edited by gjohn282; 07-14-2012 at 05:47 PM.

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

    Re: Countif Unique Records in List Match Criteria

    The cause didn't stand out easily, there is a bug either with the countifs function, or with my way of thinking how it should work in this scenario

    I'm going to look into that more and see what I can find, meanwhile an alternative that works

    PHP Code: 
    =SUM(SIGN(FREQUENCY(IF($A$2:$A$4000="Aaron",$B$2:$B$4000),IF($A$2:$A$4000="Aaron",$B$2:$B$4000)))) 
    As before, Array confirmed with Shift Ctrl Enter.

    I'm sure that's not the same method that most poeple use for FREQUENCY() formula, but it gives correct results

  5. #5
    Registered User
    Join Date
    05-09-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: Countif Unique Records in List Match Criteria

    That looks like be working now with your new formula. Thank you!!

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Countif Unique Records in List Match Criteria

    Why complicate things with complex array formulae?

    Use a helper column, say Column C
    In C2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Drag/Fill Down

    If you don't have an unique list of "Associate" names then in E2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Then in F2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Drag/Fill both Down

    If you don't need this to work with 2003 and earlier you can use COUNTIFS() instead of SUMPRODUCT()
    In C2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In E2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This will be conciderably more efficient with larger sheets.
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

+ 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