+ Reply to Thread
Results 1 to 15 of 15

How to count unique values in filtered list?

  1. #1
    Registered User
    Join Date
    10-23-2014
    Location
    Europe
    MS-Off Ver
    MS Office 2010
    Posts
    7

    Question How to count unique values in filtered list?

    Hi together,

    I have a list (colomn) which contains different values, multiple.
    Now i use this formula to count the number of different contained values:
    {=SUMME(WENN(HÄUFIGKEIT(WENN(LÄNGE(A2:A10)>0;VERGLEICH(A2:A10;A2:A10;0);""); WENN(LÄNGE(A2:A10)>0;VERGLEICH(A2:A10;A2:A10;0);""))>0;1))}

    Now I'd like to count the number of different values by filtering the list by another value.
    But unfortunately this formula ignores filtering, it counts also the hidden values.

    How must I change this formula, that it counts only visible values?
    Is there another way to solve this problem?

    THX u all for helping me!
    Cayenne

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,961

    Re: How to count unique values in filtered list?

    Hi,

    Have you thought of using

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    10-23-2014
    Location
    Europe
    MS-Off Ver
    MS Office 2010
    Posts
    7

    Re: How to count unique values in filtered list?

    Subtotal (Teilergebnis) 102 tells you how many values are in an area.

    BUT: I'd like to know how many "different" values ar in there!

    For instance, there should be following data in: 1,2,3,4,4,5,5 - The wanted solution should be: 5
    AND: If i filter off 5, then the solution should be 4.

    I hope I could clarify!

    BR, Cayenne

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to count unique values in filtered list?

    A sample workbook would be nice so we don't have to translate functions and regional settings...
    Remember what the dormouse said
    Feed your head

  5. #5
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: How to count unique values in filtered list?

    Hi All,



    Please Login or Register  to view this content.
    to be confirmed withn control+shift+enter


    Ich hoffe, es ist ein wenig Hilfe
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  6. #6
    Registered User
    Join Date
    10-23-2014
    Location
    Europe
    MS-Off Ver
    MS Office 2010
    Posts
    7

    Re: How to count unique values in filtered list?

    Here is a sample workbook.

    There are 6 different values in.
    If I filter out the "4", you can only see 5 different values remaining.

    BUT the formula counts 5 different values anyway.
    How mut the formula be adjusted, that only the visble values are counted?

    THX /C
    Attached Files Attached Files

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,409

    Re: How to count unique values in filtered list?

    Hi,

    Using a "Helper Column" is my method for doing this kind of problem. See the attached.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  8. #8
    Registered User
    Join Date
    10-23-2014
    Location
    Europe
    MS-Off Ver
    MS Office 2010
    Posts
    7

    Re: How to count unique values in filtered list?

    Hi Canapone!

    Could you be so kind and put your formula in an xls-upload?
    Cause xls translates your formula automatically...

    THXTHX C.

  9. #9
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: How to count unique values in filtered list?

    If the values to count are numeric as per your example then this formula will suffice, see attached

    =SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(A2,ROW(A2:A12)-ROW(A2),0)),A2:A12),A2:A12),1))
    Attached Files Attached Files
    Audere est facere

  10. #10
    Registered User
    Join Date
    10-23-2014
    Location
    Europe
    MS-Off Ver
    MS Office 2010
    Posts
    7

    Re: How to count unique values in filtered list?

    Hi daddylonglegs!

    THIS WORX!! 1000THX

    But unfortunately the real to be analysed data ar mixed type, text and numbers...
    (And the original file has 15.000 lines, so it needs app 30 sec to be calculated)

    Do you think there is also a solution?

    THX /C

  11. #11
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,961

    Re: How to count unique values in filtered list?

    Hi,

    I understand that the OP not only needs to know the number of unique items but also unique items when a list is filtered.
    That implies knowing the row height.

    A macro would do it but there's also the almost forgotten Excel4 functions, one of which is

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    However whilst it can't be entered directly in a cell, it can be used in a defined range name.

    The attached uses this function in a second helper column so that Marvin's 1st helper COUNTIF column can be extended to a COUNTIFS function. The file is no treated as a macro file hence the .xlsm extension.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    10-23-2014
    Location
    Europe
    MS-Off Ver
    MS Office 2010
    Posts
    7

    Re: How to count unique values in filtered list?

    Hi Richard,

    thanks a lot, what do you mean about daddylongleg's solution?
    It works properly with numbers.

    Can it be changed, so that it works with text also?
    There is no helper needed, what I think is an advantage for me...

    THX /C

  13. #13
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: How to count unique values in filtered list?

    For any type of data canapone's solution should work for you - I added to my workbook - see cell in red

    That will be slow though, so you might want to go with the helper cell solution
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    10-23-2014
    Location
    Europe
    MS-Off Ver
    MS Office 2010
    Posts
    7

    Thumbs up Re: How to count unique values in filtered list?

    THANK YOU ALL!

    I will choose the latest version of daddylonglegs, this works also with text or mixed data and it is quickly added!

    The helpers ara also a perfct method, I'll remember, I Think I'll need them for another story...

    THXTHXTHX Cayenne
    Greets from Europe

  15. #15
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,961

    Re: How to count unique values in filtered list?

    Quote Originally Posted by Cayenne View Post
    Hi Richard,

    thanks a lot, what do you mean about daddylongleg's solution?
    It works properly with numbers.


    THX /C
    Hi,

    Sorry, I should have said I was referring to Marvin's solution which was giving unique values but only for unfiltered data - at least on my system. Hence why I added a 2nd helper column to his first.

    ddl's of course works fine although as he implies array formulae may be slower with very large data sets.

    It seems a shame and presumably an oversight that MS forgot to include a row height parameter in the CELL function. It contains a column width parameter but nothing for height so we can only imply that's a mistake, particularly given Excel4s 1st generation of Get.Cell functions which does include height.



    Apologies for any confusion.

+ 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. [SOLVED] Count number of unique names in a filtered list (non array)
    By IN_FOR_SIN in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 01-31-2014, 04:07 AM
  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. [SOLVED] 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1