+ Reply to Thread
Results 1 to 3 of 3

Counting Unique Values with one criteria between a given date range?

  1. #1
    Registered User
    Join Date
    11-09-2011
    Location
    San Diego
    MS-Off Ver
    Excel 2007
    Posts
    42

    Counting Unique Values with one criteria between a given date range?

    So this is my first post on this forum, mostly because I have spent months lurking and pulling code from other posts. (That's how it should be done on a forum, right ) But I have finally gotten to a problem I haven't been able to solve myself.

    This is another unique frequency question also with two conditions. However the difference with my situation is that mine seems to combine two of the common problems, both of which I have solved independently, but have not been able to make work in conjunction.

    I need to count unique entries in a list of numbers that fall between a given date range, with one criteria. I have mocked up a sample of my data to explain.
    Attached Files Attached Files
    Last edited by magicbob007; 11-16-2011 at 02:23 PM.

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

    Re: Counting Unique Values with one criteria between a given date range?

    Hello,

    Assume users always be a valid numbers. If so try these Array Formulas, If users are not valid numbers will need to use MATCH.

    I2,

    =SUM(SIGN(FREQUENCY(IF(Table1[Criteria]="N",Table1[User]),Table1[User])))

    I3,

    =SUM(SIGN(FREQUENCY(IF(Table1[Date]>=K3,IF(Table1[Date]<=L3,Table1[User])),Table1[User])))

    I6,

    =SUM(SIGN(FREQUENCY(IF(Table1[Date]>=K3,IF(Table1[Date]<=L3,IF(Table1[Criteria]="N",Table1[User]))),Table1[User])))
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  3. #3
    Registered User
    Join Date
    11-09-2011
    Location
    San Diego
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Counting Unique Values with one criteria between a given date range?

    Oh that is elegant! Thanks for the quick reply!

    =SUM(SIGN(FREQUENCY(IF(Table1[Criteria]="N",Table1[User]),Table1[User])))

    This is beautiful...basically reduces it to a sumifs function! This would have saved me a week of searching. Thanks again.

+ 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