+ Reply to Thread
Results 1 to 8 of 8

COUNTIFS for Unique Data

  1. #1
    Registered User
    Join Date
    11-10-2016
    Location
    New York, New York
    MS-Off Ver
    2010
    Posts
    18

    COUNTIFS for Unique Data

    Hi, I'm trying to use a formula to automate finding unique clients in a certain time period.

    If Column A is all clients, Column B is dates, I'd like to get the number of unique clients we've done business with in "x" month.

    I have this formula to pull only unique values but having trouble incorporating the date piece of it.
    {=SUM(IF(1/COUNTIF(A1:A10,A1:A10)=1,1,0))}

    I also have this fomrula to pull the distinct values if it's of any help.
    {=SUM(IF(B2:B7<>"",1/COUNTIF(A1;A10, A1:A10), 0))}

    Any help would be much appreciated!!!

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    754

    Re: COUNTIFS for Unique Data

    Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

  3. #3
    Registered User
    Join Date
    11-10-2016
    Location
    New York, New York
    MS-Off Ver
    2010
    Posts
    18

    Re: COUNTIFS for Unique Data

    Attached. Ideally, I'd like formulas for the top right portion.
    Attached Files Attached Files

  4. #4
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,263

    Re: COUNTIFS for Unique Data

    Please try at
    G4
    =COUNT(1/FREQUENCY(IF($C$4:$C$16<=EOMONTH(MID(F4,11,9)&2020,0),MATCH($B$4:$B$16,$B$4:$B$16,)),ROW($B$1:$B$16)))
    confirm by press Ctrl+Shift+Enter

    H4
    =G4-N(G3)



    G14
    =COUNT(1/FREQUENCY(MATCH($B$4:$B$16,$B$4:$B$16,),ROW($B$1:$B$16)))

    H14
    =SUMPRODUCT(--(FREQUENCY(MATCH($B$4:$B$16,$B$4:$B$16,),ROW($B$1:$B$16))=1))

    FREQUENCY(MATCH()) is calculate a lot faster than COuntifs array.
    Please test with 1000 rows of data to compare.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-10-2016
    Location
    New York, New York
    MS-Off Ver
    2010
    Posts
    18

    Re: COUNTIFS for Unique Data

    I'm generally a little confused on how FREQUENCY works but I'll look into it. I gave you a somewhat simplified example, which I'm now thinking I shouldn't have haha.

    What if there's another field I need to look at (Say we lost a deal, that shouldn't count towards clients) or if I'm only looking for repeat clients between two dates (say for a particular month instead of before X date)

    I tried adjusting IF() part of formula to include AND() in the logic, but didn't seem to work. Reattached with an updated example.

    Thanks so much for your help!
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-10-2016
    Location
    New York, New York
    MS-Off Ver
    2010
    Posts
    18

    Re: COUNTIFS for Unique Data

    with attachment
    Attached Files Attached Files

  7. #7
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,263

    Re: COUNTIFS for Unique Data

    H22
    =COUNT(1/FREQUENCY(IF(($C$4:$C$16>=F22)*($C$4:$C$16<G22)*($D$4:$D$16="Won"),MATCH($B$4:$B$16,$B$4:$B$16,)),ROW($B$1:$B$16)))
    Ctrl+Shift+Enter
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-10-2016
    Location
    New York, New York
    MS-Off Ver
    2010
    Posts
    18

    Re: COUNTIFS for Unique Data

    Quote Originally Posted by Bo_Ry View Post
    H22
    =COUNT(1/FREQUENCY(IF(($C$4:$C$16>=F22)*($C$4:$C$16<G22)*($D$4:$D$16="Won"),MATCH($B$4:$B$16,$B$4:$B$16,)),ROW($B$1:$B$16)))
    Ctrl+Shift+Enter
    You are a legend!!

+ 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] COUNTIFS and unique items
    By ChrisMattock in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2018, 08:19 AM
  2. [SOLVED] Countifs + unique
    By Lsxtrkiller in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-10-2017, 01:17 AM
  3. Replies: 14
    Last Post: 03-19-2017, 08:11 AM
  4. countifs unique values
    By hasters in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-14-2016, 05:05 AM
  5. [SOLVED] Help with difficult unique countifs
    By stephme55 in forum Excel General
    Replies: 7
    Last Post: 07-08-2016, 03:42 AM
  6. Countifs unique values only
    By BCoke in forum Excel General
    Replies: 3
    Last Post: 10-16-2015, 03:45 AM
  7. Countifs for Unique Values
    By lucas813 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2015, 01:45 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