+ Reply to Thread
Results 1 to 9 of 9

Counting Unique Text Values When Filtered

  1. #1
    Registered User
    Join Date
    11-16-2013
    Location
    Los Angeles
    MS-Off Ver
    Excel 2003
    Posts
    3

    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. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,899

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

  3. #3
    Registered User
    Join Date
    11-16-2013
    Location
    Los Angeles
    MS-Off Ver
    Excel 2003
    Posts
    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!
    Attached Files Attached Files

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Counting Unique Text Values When Filtered

    Quote Originally Posted by mzagha View Post
    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?
    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

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,899

    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. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Registered User
    Join Date
    11-16-2013
    Location
    Los Angeles
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Counting Unique Text Values When Filtered

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

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Counting Unique Text Values When Filtered

    You're welcome. Thanks for the feedback!

  9. #9
    Registered User
    Join Date
    11-24-2015
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Counting Unique Text Values When Filtered

    Great solution! Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 03-14-2013, 05:03 AM
  2. [SOLVED] Counting unique values (text or number) in one column
    By djaurit in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-06-2012, 03:13 PM
  3. counting unique items(values or text)
    By guneet_ahuja in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-22-2006, 02:52 AM
  4. Counting unique text entries in a filtered list...
    By SOS in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-05-2006, 12:31 PM
  5. counting unique numbers in filtered data
    By deb in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-22-2005, 05:05 PM

Tags for this Thread

Bookmarks

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