# Thread: Couting Unique value from the Filtered Table

1. ## 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

2. Using Data--Advanced Filter to find unique entries and then doing a simple CountA

3. 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.

4. 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.

5. 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.

6. ## 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))`

7. 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.

8. 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

9. 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. Thank you.

#### Posting Permissions

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