# Counting Unique Text Values When Filtered

1. ## Counting Unique Text Values When Filtered

Hello All,

I am currently using the following formula to count unique text values in a column.

=SUM(IF(FREQUENCY(MATCH(A2:A365,A2:A365,0),MATCH(A2:A365,A2:A365,0))>0,1))

However, when I filter the information in the column, the calculation remains the same. I'd like use a formula that counts unique texts values in only the visible range, similar to a subtotal function.

Please let me know if you can help!

Thanks,
Matt  Register To Reply

2. ## Re: Counting Unique Text Values When Filtered

Hi and welcome to the forum Hard to offer suggestions without seeing a sample of what you are working with However, have you tried looking at the =subtotal() function? Perhaps of you use use the 103 code (countA), it will give you what you want? 103 counts visible rows, 3 counts all rows  Register To Reply

3. ## Re: Counting Unique Text Values When Filtered

Thanks for the quick reply! I've attached an example to this post. In the example there are 5 different unique text values in the "name" column and the "category" column is made up of the numbers 1 and 2. When I filter the category column to only show the number 2, there are only 3 corresponding unique text values in the name column. However, the formula that counts the unique texts values calculates the entire column rather than only the visible texts values in the name column.

Thanks again!  Register To Reply

4. ## Re: Counting Unique Text Values When Filtered Originally Posted by mzagha Hello All,

I am currently using the following formula to count unique text values in a column.

=SUM(IF(FREQUENCY(MATCH(A2:A365,A2:A365,0),MATCH(A2:A365,A2:A365,0))>0,1))

However, when I filter the information in the column, the calculation remains the same. I'd like use a formula that counts unique texts values in only the visible range, similar to a subtotal function.

Please let me know if you can help!

Thanks,
Matt
Just because you filtered column this does not change the count of unique values. What exactly are you trying to count? Or you need to extract unique values?  Register To Reply

5. ## Re: Counting Unique Text Values When Filtered

1 way around this would be to use a helper column (I used C, with this copied down...
=IF(COUNTIFS(\$A\$2:A2,A2,\$B\$2:B2,B2)>1,"",1)
Then you could use this for the count, based on that column...
=SUBTOTAL(102,C2:C15)  Register To Reply

6. ## Re: Counting Unique Text Values When Filtered

Try this array formula**:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A15)-ROW(A2),0)),MATCH(A2:A15,A2:A15,0)),ROW(A2:A15)-ROW(A2)+1)>0,1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.  Register To Reply

7. ## Re: Counting Unique Text Values When Filtered

Tony, you nailed it! Thanks so much for your help.  Register To Reply

8. ## Re: Counting Unique Text Values When Filtered

You're welcome. Thanks for the feedback!   Register To Reply

9. ## Re: Counting Unique Text Values When Filtered

Great solution! Thanks!  Register To Reply

#### Thread Information

##### Users Browsing this Thread

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

#### Tags for this Thread #### 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