Results 1 to 10 of 10

Thread: Couting Unique value from the Filtered Table

  1. #1
    Seeker farooq_bhatti's Avatar
    Join Date
    Jun 2020
    Posts
    5
    Articles
    0
    Excel Version
    2013

    Couting Unique value from the Filtered Table



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi There,

    I have gone through your site and found good resources for refreshing my knowledge of Excel. However I am still stuck in getting the solution of counting the unique string values from the filtered Table.




    Please note I am using Office version 2013 and filtering table based on Department.



    Can you please help me in this regards. The file is attached for your kind perusal.


    Many thank you in advance.


    Best Regards,
    Farooq
    Attached Files Attached Files

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    242
    Articles
    0
    Excel Version
    2019
    Using Data--Advanced Filter to find unique entries and then doing a simple CountA
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Capture.jpg 
Views:	12 
Size:	96.1 KB 
ID:	9896  

  3. #3
    Conjurer NormS's Avatar
    Join Date
    Jul 2017
    Posts
    122
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    Will you only be selecting a single department when you filter? If so, here's an approach using a helper column and SUBTOTAL. This wont work if you have the same company in two different departments and select both of those departments in the filter.
    Attached Files Attached Files

  4. #4
    Seeker farooq_bhatti's Avatar
    Join Date
    Jun 2020
    Posts
    5
    Articles
    0
    Excel Version
    2013
    Thank you,

    This helped but what if there are multiple criteria of filter beside department, like status of the project and the warranty in years. And if we filtered the data based on that how can we get the unique count of the company

    Please check the attached file.
    Attached Files Attached Files

  5. #5
    Conjurer NormS's Avatar
    Join Date
    Jul 2017
    Posts
    122
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    With a little help from Charles Kyd

    http://www.exceluser.com/formulas/vi...cel-tables.htm

    who shows how to create a formula to flag visible rows:

    =(AGGREGATE(3,5,[@COMPANY])>0)+0

    this appears to do a better job.
    Attached Files Attached Files

  6. #6
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    901
    Articles
    0
    Excel Version
    Excel 2013

    Couting Unique value from the Filtered Table

    Try without helper column

    for Unique Project Count (array formula finished with CSE)
    Code:
    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3;OFFSET(B2:B16;ROW(B2:B16)-ROW(B2);0;1));MATCH(B2:B16;B2:B16;0));ROW(B2:B16)-ROW(B2)+1);1))
    for Unique Company Count (array formula finished with CSE)
    Code:
    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3;OFFSET(C2:C16;ROW(C2:C16)-ROW(C2);0;1));MATCH(C2:C16;C2:C16;0));ROW(C2:C16)-ROW(C2)+1);1))
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  7. #7
    Seeker farooq_bhatti's Avatar
    Join Date
    Jun 2020
    Posts
    5
    Articles
    0
    Excel Version
    2013
    Thank you Norms,

    Yes it did the great job, working perfectly even with multiple filters.

    Also thanks to navic for giving another possible solution, I tried that and it is working awesome. However to digest this (array formula finished with CSE) seems to be tough nut to crack.

    Onceagain thank you dudes.
    Last edited by farooq_bhatti; 2020-07-02 at 06:20 AM.

  8. #8
    Seeker farooq_bhatti's Avatar
    Join Date
    Jun 2020
    Posts
    5
    Articles
    0
    Excel Version
    2013
    However I tried to use it with table refernce but can't get into it why it is not working perfectly with multiple filter

    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(tbl_Projects[COMPANY],ROWS(tbl_Projects[COMPANY])-ROW(tbl_Projects[COMPANY]),0,1)),MATCH(tbl_Projects[COMPANY],tbl_Projects[COMPANY],0)),ROWS(tbl_Projects[COMPANY])-ROW(tbl_Projects[COMPANY])+1),1))

    Please check the attached sheet
    Attached Files Attached Files

  9. #9
    Conjurer NormS's Avatar
    Join Date
    Jul 2017
    Posts
    122
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    This seems to work

    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(tbl_Projects[COMPANY],ROW(tbl_Projects[COMPANY])-ROW(tbl_Projects[[#Headers],[COMPANY]])-1,0,1)),MATCH(tbl_Projects[COMPANY],tbl_Projects[COMPANY],0)),ROW(tbl_Projects[COMPANY])-ROW(tbl_Projects[[#Headers],[COMPANY]])-1+1),1))

    replaced ROW(C2) with (ROW(tbl_Projects[[#Headers],[COMPANY]])+1)

  10. #10
    Seeker farooq_bhatti's Avatar
    Join Date
    Jun 2020
    Posts
    5
    Articles
    0
    Excel Version
    2013
    Thank you.
    Last edited by AliGW; 2020-07-06 at 06:59 AM. Reason: Please don't quote unnecessarily!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •