+ Reply to Thread
Results 1 to 15 of 15

Count the number of unique records that match a criteria

  1. #1
    Registered User
    Join Date
    04-04-2012
    Location
    Somerset, England
    MS-Off Ver
    365
    Posts
    13

    Question Count the number of unique records that match a criteria

    I have a sheet where I record episodes of sickness for staff. I want to return the total count of unique people, who have 5 or more individual episodes of sickness. For example, person A might have 5 episodes and person B might have 10 episodes. I therefore need to return the number 2, not 15, as 15 is the overall general count, where as there are only 2 unique people with more than 5 episodes of sickness or more.

    I've found some articles online suggesting a combo of ROWS, UNIQUE and FILTER, but I cannot get this to work correctly. Is there another approach or am I missing something?

    I've simplified my example in the attached and there are reasons why some of these numbers (e.g. instances) are entered manually...
    Attached Files Attached Files
    Last edited by basalisbury; 04-21-2022 at 11:16 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Count the number of unique records that match a criteria

    How do you derive your expected answer (2) from the sample sheet provided... when no-one has more than 4 episodes of illness... let alone 5+
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Count the number of unique records that match a criteria

    Maybe this?

    =LET(a,UNIQUE(A2:A12),b,SUMIF(A2:A12,a,B2:B12),ROWS(FILTER(CHOOSE({1,2},a,b),b>=5)))
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Count the number of unique records that match a criteria

    As @Glenn said on samples result is 0 as no one has more than 4 episodes of sickness.
    But this should works:

    Please Login or Register  to view this content.
    EDIT: You do not have to manually count episodes, put into B2:
    Please Login or Register  to view this content.
    and drag it down
    Last edited by KOKOSEK; 04-21-2022 at 10:35 AM.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  5. #5
    Registered User
    Join Date
    04-04-2012
    Location
    Somerset, England
    MS-Off Ver
    365
    Posts
    13

    Re: Count the number of unique records that match a criteria

    Sorry, just realised I attached before saving the example. I've attached a new copy. The instances are manually counted and added.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Count the number of unique records that match a criteria

    Have you tried my suggestion?

    @The Others


    For example, Dan S. has 19 total episodes - that's my understanding.
    Attached Files Attached Files

  7. #7
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Count the number of unique records that match a criteria

    @AliGW: I think that in col. B is consecutive appearance of sickness for particular person (that's I understand this).

  8. #8
    Forum Contributor John Vergara's Avatar
    Join Date
    01-17-2022
    Location
    Colombia
    MS-Off Ver
    365
    Posts
    200

    Re: Count the number of unique records that match a criteria

    hi to all!

    Another approach could be:
    PHP Code: 
    =SUM(--(COUNTIF(A2:A15,UNIQUE(A2:A15))>4)) 
    Blessings!

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Count the number of unique records that match a criteria

    Quote Originally Posted by KOKOSEK View Post
    @AliGW: I think that in col. B is consecutive appearance of sickness for particular person (that's I understand this).
    Who knows? Ther OP seems to be ignoring the question.

  10. #10
    Registered User
    Join Date
    11-13-2018
    Location
    Istanbul
    MS-Off Ver
    Microsoft 365
    Posts
    91

    Re: Count the number of unique records that match a criteria

    Hi. See if this works

    =COUNTA(UNIQUE(FILTER(A2:A15,(ISNUMBER(E:E))*(E:E>=5))))

    or

    =COUNTA(UNIQUE(FILTER(A2:A15;E2:E15>=5)))

    Both get the result for me.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Count the number of unique records that match a criteria

    Could be this, actually:

    =LET(a,UNIQUE(A2:A15),b,MAXIFS(B2:B15,A2:A15,a),ROWS(FILTER(CHOOSE({1,2},a,b),b>4)))

  12. #12
    Registered User
    Join Date
    04-04-2012
    Location
    Somerset, England
    MS-Off Ver
    365
    Posts
    13

    Re: Count the number of unique records that match a criteria

    Hi AliGW, this seems to be the closest when I enter it into my live s/s. although it should spit out 10 and it's spitting out 11. I'll upload an example with personal data removed.

    The total instances is manual due to older, legacy spreadsheets. So we manually count, may merge all in the future to make it automated.

  13. #13
    Registered User
    Join Date
    04-04-2012
    Location
    Somerset, England
    MS-Off Ver
    365
    Posts
    13

    Re: Count the number of unique records that match a criteria

    That's right yes. has to be manual currently due to older legacy records

  14. #14
    Registered User
    Join Date
    04-04-2012
    Location
    Somerset, England
    MS-Off Ver
    365
    Posts
    13

    Re: Count the number of unique records that match a criteria

    Quote Originally Posted by AliGW View Post
    Could be this, actually:

    =LET(a,UNIQUE(A2:A15),b,MAXIFS(B2:B15,A2:A15,a),ROWS(FILTER(CHOOSE({1,2},a,b),b>4)))
    This worked perfectly thank you, my mistake with commas

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Count the number of unique records that match a criteria

    If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

+ 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. count number of records per month based on criteria
    By nd2828 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-17-2018, 01:12 PM
  2. [SOLVED] count the number of unique text records with multiple criteria
    By Perk1961 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-22-2016, 04:30 PM
  3. Count unique records relative to multiple criteria
    By MStyles in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-15-2013, 09:56 AM
  4. [SOLVED] Countif Unique Records in List Match Criteria
    By gjohn282 in forum Excel General
    Replies: 5
    Last Post: 07-16-2012, 04:15 AM
  5. Count unique records with criteria
    By vancoservices in forum Excel General
    Replies: 4
    Last Post: 08-24-2010, 09:13 AM
  6. Formula to count unique number of records in a column
    By stats09 in forum Excel General
    Replies: 3
    Last Post: 03-22-2010, 10:15 PM
  7. Count the number of unique records
    By [email protected] in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-08-2006, 03:35 AM

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