+ Reply to Thread
Results 1 to 7 of 7

How to count only unique values? I cannot delete duplicates

  1. 11-13-2023, 01:46 PM #1
    sos_xlsx
    sos_xlsx is offline
    Registered User
    Join Date
    05-30-2023
    Location
    Portugal
    MS-Off Ver
    version 2208
    Posts
    7

    How to count only unique values? I cannot delete duplicates

    Hello!
    I have a table with thousands of lines and I need to count, in each column, the unique values. Some cells are blank. I cannot delete any lines because each one has a total in the last column, and I need to have these totals.
    Is there any function similar to the CountDistinct that Power BI has?
    Attached is a sample of the file I am working on. The actual one has dozens of thousands of lines (and will grow every month)
    Thanks in advance!Regards,
    Ana
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-13-2023, 01:51 PM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2405 (Windows 11 23H2 64-bit)
    Posts
    81,426

    Re: How to count only unique values? I cannot delete duplicates

    Maybe:

    =COUNTA(UNIQUE(FILTER(range,range<>"")))
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 11-13-2023, 02:06 PM #3
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,110

    Re: How to count only unique values? I cannot delete duplicates

    Another option
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 11-16-2023, 05:09 AM #4
    sos_xlsx
    sos_xlsx is offline
    Registered User
    Join Date
    05-30-2023
    Location
    Portugal
    MS-Off Ver
    version 2208
    Posts
    7

    Re: How to count only unique values? I cannot delete duplicates

    Thanks a lot for the help, AliGW!
    Regards,
    Ana
    Reply With QuoteRegister To Reply
  5. 11-16-2023, 05:12 AM #5
    sos_xlsx
    sos_xlsx is offline
    Registered User
    Join Date
    05-30-2023
    Location
    Portugal
    MS-Off Ver
    version 2208
    Posts
    7

    Re: How to count only unique values? I cannot delete duplicates

    Thanks a lot for the help, Fluff13!
    Regards,
    Ana
    Reply With QuoteRegister To Reply
  6. 11-16-2023, 05:14 AM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2405 (Windows 11 23H2 64-bit)
    Posts
    81,426

    Re: How to count only unique values? I cannot delete duplicates

    Glad to have helped.

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
    Reply With QuoteRegister To Reply
  7. 11-16-2023, 07:49 AM #7
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,110

    Re: How to count only unique values? I cannot delete duplicates

    Glad to help & thanks for the feedback.
    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. Count unique values excluding duplicates
    By uqls in forum Excel General
    Replies: 2
    Last Post: 04-27-2023, 04:05 AM
  2. [SOLVED] Count unique values in column with duplicates
    By rayted in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-06-2018, 04:58 AM
  3. [SOLVED] Count Unique Values with Duplicates in Multiple Columns
    By sTeezZy in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-26-2016, 05:50 PM
  4. [SOLVED] Macro to find duplicates, concatenate Unique Values, then delete old duplicates
    By lesoies in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-17-2013, 04:32 PM
  5. [SOLVED] Macro to count unique values in a column, enter it in next column, then delete duplicates
    By pmorisse in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-25-2013, 03:27 PM
  6. [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
  7. Count unique values among duplicates in a subtotal range
    By jcpotwor in forum Excel General
    Replies: 2
    Last Post: 01-12-2006, 09:35 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1