+ Reply to Thread
Results 1 to 13 of 13

Count unique values that meet multiple criteria

  1. 06-09-2021, 11:18 AM #1
    anandis
    anandis is offline
    Registered User
    Join Date
    08-02-2020
    Location
    nerul,mahrashtra
    MS-Off Ver
    Excel 2016
    Posts
    43

    Count unique values that meet multiple criteria

    I've a simple table as below but with a lot of data. I need to get the numbers of staff per category using an excel formula while making sure I dont count duplicate entries.
    Total number of category V should be 2 . Using Countif 
    Please Login or Register  to view this content.
    I get 3 which is wrong (as v10 appears twice, it must be considered only once)
    How do I define the second criteria - of ignoring multiple occurences of staffid and using just the first ?

    S. No StaffID Name Category
    1 Nil P1 R
    2 v81 P2 V
    3 v10 P3 V
    4 s71 P4 S
    5 b47 P5 B
    6 b62 P6 B
    7 v10 P3 V
    Thanks
    A
    Reply With QuoteRegister To Reply
  2. 06-09-2021, 11:31 AM #2
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,804

    Re: Count unique values that meet multiple criteria

    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.


    may be


    A
    B
    C
    1
    		 StaffID Name Category
    2
    		 Nil P1 R
    3
    		 v81 P2 V
    4
    		 v10 P3 V
    5
    		 s71 P4 S
    6
    		 b47 P5 B
    7
    		 b62 P6 B
    8
    		 v10 P3 V
































    E2=SUM(IF(FREQUENCY(IF(C2:C8=C3,MATCH(A2:A8,A2:A8,0)),ROW(C2:C8)-ROW(C2)+1),1))


    Control shift+enter
    Last edited by CARACALLA; 06-09-2021 at 11:38 AM.
    Reply With QuoteRegister To Reply
  3. 06-10-2021, 12:29 AM #3
    anandis
    anandis is offline
    Registered User
    Join Date
    08-02-2020
    Location
    nerul,mahrashtra
    MS-Off Ver
    Excel 2016
    Posts
    43

    Re: Count unique values that meet multiple criteria

    Yes ! It works. I didn't know how to use frequency. (Will keep in mind adding the workbook)

    Thanks so much .
    Reply With QuoteRegister To Reply
  4. 06-14-2021, 04:47 AM #4
    anandis
    anandis is offline
    Registered User
    Join Date
    08-02-2020
    Location
    nerul,mahrashtra
    MS-Off Ver
    Excel 2016
    Posts
    43

    Re: Count unique values that meet multiple criteria

    Ok I got into a bit of a jumble.

    Please see the two cells in Yellow. How do I extend the above frequency formula to check for two criteria?
    1. If StaffID is duplicate (which was solved earlier)
    and having got that to check
    2. The status of a staffid .

    I need to count them separately.

    Thanks
    A
    Attached Files Attached Files
    Last edited by anandis; 06-14-2021 at 04:55 AM.
    Reply With QuoteRegister To Reply
  5. 06-14-2021, 05:21 AM #5
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,504

    Re: Count unique values that meet multiple criteria

    Try for "In"

    =SUM(IF(Table1[In/Out/Leave]="In",1/COUNTIFS(Table1[In/Out/Leave],"In",Table1[StaffID],Table1[StaffID])))

    Array formula, confirmed with Ctrl-Shif-Enter, not Enter only.

    Similar to "Out","Leave"
    Quang PT
    Reply With QuoteRegister To Reply
  6. 06-14-2021, 07:14 AM #6
    anandis
    anandis is offline
    Registered User
    Join Date
    08-02-2020
    Location
    nerul,mahrashtra
    MS-Off Ver
    Excel 2016
    Posts
    43

    Re: Count unique values that meet multiple criteria

    No it doesn't seem to work. I get a count of 12 when it should be 59
    I've added more data - please see Table 2 .
    In should be 59
    Attached Files Attached Files
    Last edited by anandis; 06-14-2021 at 07:46 AM. Reason: Adding attachment
    Reply With QuoteRegister To Reply
  7. 06-14-2021, 10:58 AM #7
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,804

    Re: Count unique values that meet multiple criteria

    H20=COUNTIFS(Table2[StaffID],"<>",Table2[In/Out/Leave],"In")


    This is 59 with duplicate

    12 without duplicate
    Reply With QuoteRegister To Reply
  8. 06-14-2021, 12:46 PM #8
    anandis
    anandis is offline
    Registered User
    Join Date
    08-02-2020
    Location
    nerul,mahrashtra
    MS-Off Ver
    Excel 2016
    Posts
    43

    Re: Count unique values that meet multiple criteria

    Thanks a lot for your reply but it doesn't seem to work for "Out" which has 84 entries ( see column P in Table2)
    but the formula gives 89.

    I've highlighted in yellow the IDs which are getting counted twice . The IDs in green are unique and all of "NotApp"
    has to get counted.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 06-14-2021, 01:47 PM #9
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,804

    Re: Count unique values that meet multiple criteria

    Filter the table by OUT. The result is 89
    Reply With QuoteRegister To Reply
  10. 06-14-2021, 01:55 PM #10
    anandis
    anandis is offline
    Registered User
    Join Date
    08-02-2020
    Location
    nerul,mahrashtra
    MS-Off Ver
    Excel 2016
    Posts
    43
    I need it to return 84 not 89.
    Reply With QuoteRegister To Reply
  11. 06-14-2021, 01:57 PM #11
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,804

    Re: Count unique values that meet multiple criteria

    Why if out count is 89 ?
    Reply With QuoteRegister To Reply
  12. 06-14-2021, 02:26 PM #12
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,213

    Re: Count unique values that meet multiple criteria

    Please try

    =COUNT(1/FREQUENCY(IF((Table2[In/Out/Leave]="Out")*(Table2[StaffID]<>"NonApp"),MATCH(Table2[StaffID],Table2[StaffID],)),ROW(Table2)-ROW(Table2[#Headers])))+COUNTIFS(Table2[In/Out/Leave],"Out",Table2[StaffID],"NonApp")
    Ctrl+Shift+Enter

    or

    =SUMPRODUCT((Table2[In/Out/Leave]="out")*(Table2[StaffID]<>"Nonapp")/COUNTIFS(Table2[StaffID],Table2[StaffID],Table2[In/Out/Leave],Table2[In/Out/Leave]))+COUNTIFS(Table2[In/Out/Leave],"Out",Table2[StaffID],"NonApp")

    Normal enter but slower
    Attached Files Attached Files
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  13. 06-15-2021, 09:20 AM #13
    anandis
    anandis is offline
    Registered User
    Join Date
    08-02-2020
    Location
    nerul,mahrashtra
    MS-Off Ver
    Excel 2016
    Posts
    43

    Re: Count unique values that meet multiple criteria

    Thanks so much ! It works !
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. count formula for unique ids that meet multiple criteria
    By fgbdrum in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-02-2020, 03:43 AM
  2. Count Unique Values that Meet a Criteria
    By DD1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-09-2016, 11:23 PM
  3. How to calculate unique count of values in a column that does not meet a criteria.
    By Vishnu Balan in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-08-2014, 04:05 PM
  4. Counting unique values that meet multiple criteria
    By msworkman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-08-2013, 11:15 PM
  5. [SOLVED] Count Unique Values in Column A that meet criteria in Column B
    By ashakespeare in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-04-2012, 07:33 PM
  6. Count Unique Values that Meet Criteria AT LEAST TWICE
    By yoft in forum Excel General
    Replies: 25
    Last Post: 05-31-2012, 08:03 PM
  7. Count unique distinct values that meet multiple criteria in closed workbook
    By efernandes67 in forum Excel General
    Replies: 5
    Last Post: 03-13-2012, 06:05 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1