+ Reply to Thread
Results 1 to 19 of 19

count distinct value in range with filtered data

  1. #1
    Forum Contributor
    Join Date
    02-14-2015
    Location
    Milan
    MS-Off Ver
    2013
    Posts
    110

    count distinct value in range with filtered data

    SnipImage.JPG

    dear all ,
    I have the attached table .
    I need to count the unique and distinct value as shown in the example .
    the table is a filtered one so I also need to count only the visible cells .
    I found some solution using formula but only if data are stored in single column .
    can someone help please ?

    MAny tks to all
    Rgds
    Lorenzo

  2. #2
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: count distinct value in range with filtered data

    Try this code
    Please Login or Register  to view this content.
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  3. #3
    Forum Contributor
    Join Date
    02-14-2015
    Location
    Milan
    MS-Off Ver
    2013
    Posts
    110

    Re: count distinct value in range with filtered data

    HI, MANY TKS
    I have tried to run some test abd sometimes it provide me correct count sometimes not ...in some cases it show me 0 when instead I still have some visible values in the cells....

  4. #4
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: count distinct value in range with filtered data

    It is better to provide sample of your file and spot on the desired range and the expected output

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: count distinct value in range with filtered data

    I don't THINK that there is an easy way to do this with a formula. Maybe someone will prove me wrong, though!! One way would be to complie a list of unique values and then count them...

    To compile list:
    =IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW($A$2:$C$6)*100+COLUMN($A:$C))/(($A$2:$C$6<>"")*(COUNTIF($E$1:E1,$A$2:$C$6)=0)),1),"R0C00"),FALSE),"")

    To count the items in the list:
    =SUMPRODUCT(--(LEN($E$2:$E$17)>0))

    see sheet.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  6. #6
    Forum Contributor
    Join Date
    02-14-2015
    Location
    Milan
    MS-Off Ver
    2013
    Posts
    110

    Re: count distinct value in range with filtered data

    hi Glenn ,
    tks for your effort but is too complex as in the real file I have approx 2000 rows to be cheched..
    something like YasserKhalil code would be perfect .
    here attch an example of exel file .
    after apply filter in column A I need to count unique values of filtered range from C9 to I16
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: count distinct value in range with filtered data

    Try
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    02-14-2015
    Location
    Milan
    MS-Off Ver
    2013
    Posts
    110

    Re: count distinct value in range with filtered data

    hi Jindon,
    is there a way to have your function working without populate a list ?
    a msgbox with the total unique number is more than fine .
    many many tks

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: count distinct value in range with filtered data

    If the items are too many, msgbox will not display all of the result.
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    02-14-2015
    Location
    Milan
    MS-Off Ver
    2013
    Posts
    110

    Re: count distinct value in range with filtered data

    Hi Jindon,
    tks again
    I just need a msgbox showing the total number of the unique items ... .for example 12 or 15 or 6 etc etc etc

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: count distinct value in range with filtered data

    Ahh, then change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    02-14-2015
    Location
    Milan
    MS-Off Ver
    2013
    Posts
    110

    Re: count distinct value in range with filtered data

    hi Jindon ,on the test worksheet now works..many many tks
    I have adapted the cote to the real workbook but I miss something ...
    here attch my real workbook .
    check sheet2 .
    after apply filter to column "Q" I need to launch the macro and count the unique values in the visible cells from T to X columns (I have highlited in yellow)
    Attached Files Attached Files

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: count distinct value in range with filtered data

    Try change to
    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    02-14-2015
    Location
    Milan
    MS-Off Ver
    2013
    Posts
    110

    Re: count distinct value in range with filtered data

    done but hen testing no msgbox pop up ....

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: count distinct value in range with filtered data

    Should...................
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    02-14-2015
    Location
    Milan
    MS-Off Ver
    2013
    Posts
    110

    Re: count distinct value in range with filtered data

    it works without filtering.
    once I apply a filter in column Q and push the button nothing appear ...

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: count distinct value in range with filtered data

    Ahh, OK
    Please Login or Register  to view this content.

  18. #18
    Forum Contributor
    Join Date
    02-14-2015
    Location
    Milan
    MS-Off Ver
    2013
    Posts
    110

    Re: count distinct value in range with filtered data

    WOW....IT WORKS ...
    tks so much for your help..

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: count distinct value in range with filtered data

    You are welcome and thanks for the rep.

+ 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] Distinct count of a value in a range
    By pantherfanrh in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-20-2017, 09:43 AM
  2. Count only distinct observation based in ID - data in long format
    By Marvin85 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-27-2016, 11:51 AM
  3. Replies: 1
    Last Post: 12-17-2015, 11:22 AM
  4. [SOLVED] Count distinct values where data changes everyday
    By jonathan.haynes in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-24-2013, 02:56 PM
  5. Distinct count of a dynamic range
    By aaanenson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-05-2013, 11:18 AM
  6. Replies: 1
    Last Post: 03-21-2012, 07:06 PM
  7. Replies: 2
    Last Post: 10-05-2011, 12:30 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