+ Reply to Thread
Results 1 to 10 of 10

Count Unique values after being filtered

  1. 11-04-2014, 08:41 PM #1
    tylert
    tylert is offline
    Registered User
    Join Date
    11-04-2014
    Location
    Ontario, Canada
    MS-Off Ver
    Office 2010
    Posts
    6

    Question Count Unique values after being filtered

    Hello.

    I have been stumbling with this all day and I've not been able to figure it out.

    Screen Shot 2014-11-04 at 7.36.58 PM.png

    For example:
    Hit the filter on "Type" and select "house" only
    The value given is 4 which is the number of unique in the entire column, however that is not accurate after filter is applied
    The value for unique numbers is actually 3
    Also then if it is a Dwelling then it needs to be subtracted from the number in cell A13
    So Cell C13 would have to be subtracted from the value of cell A13

    Formula being used in cell A13 is =SUM(IF(FREQUENCY(A2:A11, A2:A11)>0,1))-C13

    In this case the answer I am looking for in cell A13 is 2 however, I am unable to figure out a formula to give me that.

    Help is appreciated.
    Last edited by tylert; 11-04-2014 at 09:06 PM.
    Reply With QuoteRegister To Reply
  2. 11-04-2014, 09:12 PM #2
    DJTRIN
    DJTRIN is offline
    Registered User
    Join Date
    02-09-2014
    Location
    POS, Trinidad
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Count Unique values after being filtered

    I am new to the forum but i have been working with excel for quite a while.

    I am not sure i understand what you are trying to do.

    Do you want a unique count for the type column?
    Reply With QuoteRegister To Reply
  3. 11-04-2014, 09:18 PM #3
    tylert
    tylert is offline
    Registered User
    Join Date
    11-04-2014
    Location
    Ontario, Canada
    MS-Off Ver
    Office 2010
    Posts
    6

    Re: Count Unique values after being filtered

    So just visually looking at this example you can see the numbers, I just can't get a formula to do it.

    If I use the filter to filter out all but houses that will yeild
    Screen Shot 2014-11-04 at 8.16.19 PM.png
    You can see here that the value of unique numbers beside the value "house" is 3. However, the value shown in cell A13 is 4. It is still counting the value in cell A11 as unique even though it is not listed once the filter was applied.

    make sense ? Essentially now the answer I am looking for in cell A13 is 3. (I took out column c to simplify the matter for the time being)
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  4. 11-04-2014, 10:20 PM #4
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Count Unique values after being filtered

    pl see file.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 11-04-2014, 10:23 PM #5
    tylert
    tylert is offline
    Registered User
    Join Date
    11-04-2014
    Location
    Ontario, Canada
    MS-Off Ver
    Office 2010
    Posts
    6

    Re: Count Unique values after being filtered

    Quote Originally Posted by kvsrinivasamurthy View Post
    pl see file.
    Hey, Unfortunately that doesn't address the problem with it being filtered. That is the whole issue there. I can get it to count how many of each there are, but that isn't what I want. Once I sort it using the filter then I want it count the number of unique numbers in column A. In my example the answer would be 3, however, it is still counting 4 as it is counting Cell A11 even though it isn't displayed once filtered.
    Reply With QuoteRegister To Reply
  6. 11-04-2014, 10:33 PM #6
    tylert
    tylert is offline
    Registered User
    Join Date
    11-04-2014
    Location
    Ontario, Canada
    MS-Off Ver
    Office 2010
    Posts
    6

    Re: Count Unique values after being filtered

    examplefile.xlsx

    This is the file
    Reply With QuoteRegister To Reply
  7. 11-04-2014, 10:45 PM #7
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Count Unique values after being filtered

    Try this formula courtesy of Aladin Akyurek

    =SUM(IF(FREQUENCY(IF(SUBTOTAL(103,OFFSET(A2,ROW(A2:A11)-ROW(A2),,1)),IF(A2:A11<>"",MATCH("~"&A2:A11,A2:A11&"",0))),ROW(A2:A11)-ROW(A1)+1),1))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    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
    Reply With QuoteRegister To Reply
  8. 11-04-2014, 10:51 PM #8
    tylert
    tylert is offline
    Registered User
    Join Date
    11-04-2014
    Location
    Ontario, Canada
    MS-Off Ver
    Office 2010
    Posts
    6

    Re: Count Unique values after being filtered

    I saw that on another forum. I tried that on this cell. It returns a value of 2 which I'm not sure how or why.
    Reply With QuoteRegister To Reply
  9. 11-04-2014, 11:01 PM #9
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Count Unique values after being filtered

    Try this one. It should return 3

    =SUM(IF(FREQUENCY(IF(SUBTOTAL(103,OFFSET(A2,ROW(A2:A11)-ROW(A2),,1)),IF(A2:A11<>"",MATCH(A2:A11,A2:A11,0))),ROW(A2:A11)-ROW(A2)+1),1))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Reply With QuoteRegister To Reply
  10. 11-04-2014, 11:05 PM #10
    tylert
    tylert is offline
    Registered User
    Join Date
    11-04-2014
    Location
    Ontario, Canada
    MS-Off Ver
    Office 2010
    Posts
    6

    Re: Count Unique values after being filtered

    Well admittedly I don't understand it but it does seem to work. Thank you
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to count unique values in filtered list?
    By Cayenne in forum Excel Charting & Pivots
    Replies: 14
    Last Post: 10-23-2014, 04:58 PM
  2. Count unique, visible values in a filtered column
    By kajakk in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-07-2013, 08:42 AM
  3. count unique values in filtered column
    By mcdermott2 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-09-2013, 10:40 AM
  4. Count Unique Values in a Filtered Column
    By mashley in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-30-2008, 10:56 AM
  5. Count Unique Values In A Filtered Row with Duplicates
    By jcpotwor in forum Excel General
    Replies: 1
    Last Post: 01-12-2006, 09:10 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1