Couting Unique value from the Filtered Table

farooq_bhatti

New member
Joined
Jun 30, 2020
Messages
5
Reaction score
0
Points
0
Excel Version(s)
2013
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
 

Attachments

  • Projects.xlsx
    12.1 KB · Views: 15
Using Data--Advanced Filter to find unique entries and then doing a simple CountA
 

Attachments

  • Capture.jpg
    Capture.jpg
    96.1 KB · Views: 18
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.
 

Attachments

  • ProjectsWithHelperColumn.xlsx
    13.5 KB · Views: 12
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.
 

Attachments

  • ProjectsWithHelperColumn.xlsx
    13.6 KB · Views: 12
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))
 
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. :frusty:

Onceagain thank you dudes.
 
Last edited:
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
 

Attachments

  • ProjectsWithHelperColumn .xlsx
    14.1 KB · Views: 10
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)
 
Thank you.
 
Last edited by a moderator:
Back
Top