# 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

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

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!

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?

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)

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.

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

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

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

You're welcome. Thanks for the feedback!

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

Great solution! Thanks!

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