Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Thread: Count Unique values in a filtered Column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2010
    Location
    Cincinnati,OH
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Count Unique values in a filtered Column

    I have people's names in Cells A9 to A42 and I want to count the number of unique people in the list so I can multiply the number of unique people by 40 and give me the total # of hours I have available in a week given a 40 hour work week.

    I came up with the following formula which works great except the name column is filtered so when I select a filter value I get the same number no matter what the filter is set to.

    =SUM(IF(FREQUENCY(IF(LEN(A9:A42)>0,MATCH(A9:A42,A9:A42,0),""), IF(LEN(A9:A42)>0,MATCH(A9:A42,A9:A42,0),""))>0,1,0))

    So I tried the following:
    =SUBTOTAL(9,(IF(FREQUENCY(IF(LEN(A9:A42)>0,MATCH(A9:A42,A9:A42,0),""), IF(LEN(A9:A42)>0,MATCH(A9:A42,A9:A42,0),""))>0,1,0)))

    This doesn't work because I don't think the subtotal function works with an array (not sure about that though).

    I also tried something like this
    =SUM(IF(FREQUENCY(IF(LEN(A9:A42)>0,(IF(SUBTOTAL(3,OFFSET(A9,ROW(A9:A42)-ROW(A9),0)),MATCH(A9:A42,A9:A42,0),)),""),(IF(LEN(A9:A42)>0,MATCH(A9:A42,A9:A42,0),""))),1)) and it seemed to work but if the filter value doesn't have the first person in my rows as part of the selection it always comes back with a number 1 higher than it should.

    I'm stumped. Is there a way to change the array to a list so the first subtotal function I posted will work or is there a better way altogether to accomplish what I want?

    Thank you

  2. #2
    Board Regular jim may's Avatar
    Join Date
    Jul 2004
    Location
    Roanoke, VA
    Posts
    7,449
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Unique values in a filtered Column

    Perhaps this will help...

    Sheet1
    ABC
    1Bob4
    2Ted
    3Carol
    4Alice
    5Ted
    6Bob
    7Carol
    8Ted
    9Carol
    10Ted
    Excel 2007

    Array Formulas
    CellFormula
    C1=SUM(1/COUNTIF(A1:A10,A1:A10))
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

    To display your spreadsheet data use either MrExcel HTML Maker or Excel Jeanie

  3. #3
    New Member
    Join Date
    Apr 2010
    Location
    Cincinnati,OH
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Unique values in a filtered Column

    Thank you but this won't work for a couple of reasons. The column is filtered so I don't want the unique count to add up names that are not selected in the filter. The sum feature adds the values whether they are showing up under the filter or not.

    The second reason it won't work is I need to make sure it ignores blanks fields. The formula you suggested will return a divide by 0 error is blanks exist. I can get around that with some if statements but It still won't return the right value with the filter active.

    Thanks anyway

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Count Unique values in a filtered Column

    Control+shift+enter, not just enter:
    Code:
    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A9,ROW(A9:A42)-ROW(A9),,1)), 
            IF(A9:A42<>"",MATCH("~"&A9:A42,A9:A42&"",0))),ROW(A9:A42)-ROW(A9)+1),1))
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    New Member
    Join Date
    Apr 2010
    Location
    Cincinnati,OH
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Unique values in a filtered Column

    Man. That works like a champ! What is the significants of the MATCH function with the "~" in it. I want to try to understand this more so I can do more than just copy this formula and have it work

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Count Unique values in a filtered Column

    Quote Originally Posted by webharvest View Post
    Man. That works like a champ!
    Great. Thanks for providing feedback.

    What is the significants of the MATCH function with the "~" in it. I want to try to understand this more so I can do more than just copy this formula and have it work
    It prevents the side-effects of the special chars like *, <, etc. which can occur around the entries. If such is guaranteed not to occur, the following will do just as well:

    Code:
    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A9,ROW(A9:A42)-ROW(A9),,1)), 
         IF(A9:A42<>"",MATCH(A9:A42,A9:A42,0))),ROW(A9:A42)-ROW(A9)+1),1))
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    New Member
    Join Date
    Apr 2010
    Location
    Cincinnati,OH
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Unique values in a filtered Column

    Ahhhh!!!

    One last question. I used the formula evaluated in excel because I wasn't getting how the offset function was working in the subtotal. Interestingly enough when I click through the evaluator it shows the OFFSET(A9,ROW(A9:A46)-ROW(A9),,1) in the subtotal as equally a bunch a bunch of #VALUE! (ie {#VALUE!;#VALUE!........} but when I step through the subtotal function it comes back with 0s and 1s like I would think it would.

    Why is that?

    Thanks for helping me understand.......

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Count Unique values in a filtered Column

    Quote Originally Posted by webharvest View Post
    Ahhhh!!!

    One last question. I used the formula evaluated in excel because I wasn't getting how the offset function was working in the subtotal. Interestingly enough when I click through the evaluator it shows the OFFSET(A9,ROW(A9:A46)-ROW(A9),,1) in the subtotal as equally a bunch a bunch of #VALUE! (ie {#VALUE!;#VALUE!........} but when I step through the subtotal function it comes back with 0s and 1s like I would think it would.

    Why is that?

    Thanks for helping me understand.......
    See the explanation of Laurent Longre who devised the Subtotal/Offset expression:

    http://j-walk.com/ss/excel/eee/eee001.txt
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,623
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Count Unique values in a filtered Column

    Quote Originally Posted by webharvest View Post
    Thank you but this won't work for a couple of reasons. The column is filtered so I don't want the unique count to add up names that are not selected in the filter. The sum feature adds the values whether they are showing up under the filter or not.

    The second reason it won't work is I need to make sure it ignores blanks fields. The formula you suggested will return a divide by 0 error is blanks exist. I can get around that with some if statements but It still won't return the right value with the filter active.

    Thanks anyway
    If the "blank" cells are EMPTY cells...

    Array entered**

    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A9,ROW(A9:A42)-ROW(A9),0,1)),MATCH(A9:A42,A9:A42,0)),ROW(A9:A42)-ROW(A9)+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

    Don't be afraid to use volatile functions or array formulas
    Tell us what version of Excel you're using
    KISS - Keep It Simple Stupid

  10. #10
    New Member
    Join Date
    Apr 2017
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count Unique values in a filtered Column

    Hello,

    I am also trying to do the same thing and navigate to this topic. What a brilliant idea!!

    However, for the blank cell calculation, I have further question on this. I also need to exclude those "Blank" cells, however, those "Blank" cells are not actually "Empty", the range is a calculated field in which I used IF formula like below:

    =IF(A1>10, "Count", "")

    Is there a way to further exclude these "Null" value?

    Thanks.

    Quote Originally Posted by T. Valko View Post
    If the "blank" cells are EMPTY cells...

    Array entered**

    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A9,ROW(A9:A42)-ROW(A9),0,1)),MATCH(A9:A42,A9:A42,0)),ROW(A9:A42)-ROW(A9)+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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •