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
Hi and welcome to the forum
Hard to offer suggestions without seeing a sample of what you are working withHowever, 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
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
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!
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?Originally Posted by mzagha
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
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)
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.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Tony, you nailed it! Thanks so much for your help.
You're welcome. Thanks for the feedback!
Great solution! Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks