Comments on: How to count unique values in Excel an easy way

A couple of years ago, we discussed various ways to count unique and distinct values in Excel by using traditional array formulas. Today, we will show you a much simpler solution with the recently introduced dynamic array functions. Continue reading →

Comments page 2. Total comments: 91

  1. Hi Alex,

    In a column A i have many duplicate or repeated entries i want only the first entry to be counted as 1 in column B and the rest duplicate entries as 0. can u please help me.

    Example
    Column A Column B
    12345 1
    12345 0
    23456 1
    23456 0
    12345 0
    23456 0

      1. Hi Alex,

        Thank you so much Alex,

        there is 1 more problem Can u please also help me for the below

        Column A Column B Column C (Result)

        John 12345
        John 12345
        John 12345
        Peter 67890
        Peter 67890
        Peter 67890

        I want only the first entry to be counted as 1 in column B and the rest duplicate entries as 0.
        I want result in single Column C without introducing new column.
        I tried doing concatenate but not working can u please help.

          1. Thank you so much

  2. Hi Alex,

    I have successfully applied the UNIQUE function, but I want to see how many times all unique (distinct) inputs were present in the original column. For example:
    A
    1
    1
    2

    The UNIQUE formula would give me: 1 and 2. In addition I want to see how many times the 1 and the 2 values were present in column A. Is this possible?

    Thanks, Daniel

  3. Hi,

    I am looking for a method of how to return a list of cells and count with a partial match. I have used the formula below for returns with cells for a complete match.

    =IFERROR(INDEX($A$2:$A$115, MATCH(0, IF($C$2:$C$115="monitor", COUNTIF($H$6:$H6, $A$2:$A$115), ""), 0)),"")

    I have used the COUNTIFS function to list the count for the partial matches but it is not replicating the same outcome with partial matches - is this possible?

    Thanks, Tom

    1. Hello!
      To count the number of partial matches of the word "monitor" in a range, use a formula like this:

      =SUM(--(ISNUMBER(SEARCH("monitor",B1:B25,1))))

      I hope this will help.

      1. Hi Alex,

        Apologies I forgot to mention that the list would be pulling the data from adjacent cells and then populating the lists, i.e. column A has variables and column B has comments.
        - The formula I used in the opening statement pulls from a complete match
        - The formula I need would be with a partial match

        I have sent an email to support address with the workbook if it makes more sense!

        Thanks, Tom

  4. I am trying to count unique value with criteria. The criteria are to recognize the word starting with letter A in the second column. I changed the function FIND by SEARCH to use the wildcat but it is not working, maybe you can advise. I
    IFERROR(ROWS(UNIQUE(FILTER(G2:G2367,ISNUMBER(SEARCH("*A*",H2:H2367,1))))), 0)

    1. Hello!
      Use a formula like this:

      =ROWS(UNIQUE(FILTER(A2:A10, IFERROR(SEARCH("A",B2:B10,1)=1,FALSE))))

      I hope it’ll be helpful.

  5. I have many columns where each one has different dates
    For example Column A
    Box A1 is 02/24/2021
    Box A2 is 02/25/2021
    Box A3 is 02/26/2021
    Box A2 is 02/24/2021
    I should Know the date 24 how many times is found in this Column and put it in a Box and Multiply it By 5$
    So: IF Column A has let's say 3 times (02/24/2021) that means 3 x 5$ should be equal to 15$
    How Can I do this???

    1. Hi,
      To count how many times a value appears in column A, use the COUNTIF function.

      =COUNTIF(A1:A100,D1)

      where D1 -- 02/24/2021

      I hope I answered your question. If something is still unclear, please feel free to ask.

  6. Hi,
    I have data, let's say A to F, A to E is the criteria and i need to count distinct or unique from column F with criteria from A to E, but all i got is not how much distinct counts, it's only count how much times the duplicate is. Can you help me ?

      1. Hi, thank you for your feedback.
        I already tried that way but i think i have different data so i'm a bit confused where to put. Refer to your link, data has repetition, while i already deleted duplicate ones so it's unique. Can i still use that formulas and put it in C2 ?

  7. Hello,

    Im trying to count all cells with unique values within a column range (K2:K101) excluding any cells with value 0.
    I believe the formula should look something like =COUNTA(UNIQUE(FILTER(K2:K101, K2:K101"0"))) however this consistently gives me incorrect results.
    Do you have suggestions?

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =COUNTA(UNIQUE(FILTER(K2:K101, K2:K101<>0)))

      I hope my advice will help you solve your task.

  8. I want to detect if there are multiple offer types listed in column F so I basically want to detect all unique cells in column F containing the word "offer" (unique in the sense that if some text with "offer" occurs multiple times it should only be considered once). I experimented based on your article and came up with this which worked:
    =SUM(--ISNUMBER(SEARCH("offer",UNIQUE($F:$F))))

    Is it possible to do something similar but across multiple worksheets? Doing this didn't work:
    =SUM(--ISNUMBER(SEARCH("offer",UNIQUE('Sheet1:Sheet3'!$F:$F))))

    Thanks!

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IFERROR(ROWS(UNIQUE(FILTER(F:F, ISNUMBER(FIND("offer",F:F,1))))),0)

      I hope it’ll be helpful.

  9. As usual, your tutorial gets directly to the point in a friendly manner. I encourage everyone to follow your tutorials 'cause they present the steps needed to accomplish a certain task in Excel in an easy way.

    Keep up the great work, and many thanks for the professional work you spread around with your tutorials!

    1. Dear Dr. Choueiri,

      Thank you very much for your feedback! I am happy to hear you had a positive experience with our tutorials. We will do our best to keep up and (hopefully!) improve.

  10. Hi ,
    I need help with count distinct in the below table-
    Variant Subcat CustomerCode CustomerName ProductName Jan Feb
    CREAM CREAM 6079 AL AHLI S/M CHOCOLATE CREAM 11105026 21 5
    CREAM CREAM 6079 AL AHLI S/M MANGO CREAM 11105029 21
    CREAM CREAM 6079 AL AHLI S/M ORANGE CREAM 11105028 21 4
    CREAM CREAM 99 AL DHAFRA SM ORANGE CREAM 11105028 21
    CREAM PROMO 99 AL DHAFRA SM CREAM BISC 11205001 269 30
    CREAM CREAM 7935 AL DOURIZ CHOCOLATE CREAM 11105026 21
    CREAM CREAM 7935 AL DOURIZ MANGO CREAM 11105029 22 4
    CREAM CREAM 4900 AL MADINA HYPERMARKET MANGO CREAM 11105029 21
    CREAM CREAM 4900 AL MADINA HYPERMARKET ORANGE CREAM 11105028 21

    CREAM CREAM No of customers who purchased Variant Cream 4 3
    CREAM PROMO 1 1

    I need to count the no of customers who purchased cream under each month , irrespective of the flavor.
    In the table , no of people who purchased cream was Jan - 4 , feb -3
    Similarly people who purchased cream promo was Jan =1 and Feb =1.
    Countif function counts all the product names as well .

    1. Hello!
      If the Jan and Feb columns are the numbers of buyers, then you can use the SUMIFS function to find the sum for those columns with certain conditions.
      I hope it’ll be helpful.

  11. Why if I put this formula
    =IFERROR(ROWS(UNIQUE(FILTER(C2:C210,J2:J210=J2))), 0), it get me some value
    if I increase the range from 210 to for example 400... It gives me 0 as result?
    thanks

    1. Hello!
      As my personal experience shows, the UNIQUE function does not work correctly with large data sets. It returns 0, as you did.

      1. Oh, that's great; I'm working with 66,000 rows and for hours cannot figure out why I get zero's.

        At least I can move on after reading this comment, thanks.

  12. Hi,
    Your formulas an explanantions are very helpful thanks! I am struggling with getting the correct results and I'm not sure where the issue is. This is the formula I'm using, and it's giving me a reulst of 0 for all...
    =IFERROR(ROWS(UNIQUE(FILTER(Table_SDCdata[Site], (Table_SDCdata[Format]=[@Format]) * (Table_SDCdata[Region]=[@Region])))), 0)

    Just for the data size example: Main table I'm counting and filtering has 37 columns and 140 500 rows.
    So it's suppose to count the unique 'Site' values in the Main table (becuase there are many duplicates) if the 'Region' and 'Format' matches that in the current table.
    I used "=COUNTA(UNIQUE(FILTER(" before but it was giving a value of "1" for everything if I use it on big data sets, for smaller ones it works. From what I read online "COUNTA" doesn't work well with big data sets, so I have to find an alternative. Any reason as to why it's not working? Will it also be because of the data size?

    1. Hi,

      Thank you in advance for the help: I've emailed the sample file as requested.

      What I'm trying to achieve is the following: Count all the unique 'Site' codes in the Master table, if the 'Region' and 'Format' matches that from the template table. There are many duplicates in the Masterfile for each site that matches the region and format, therefore I only want to count unique once. A small example (in case anyone else reads the post for future):
      MASTER TEMPLATE
      Format Region Site Format Region Count
      Corp EC EC1 Corp EC 2 (This would be EC1 and EC2)
      Corp WC WC1 Fran EC 1
      Fran EC EF1 Exp EC 1
      Exp EC EE1 Fran EC 1
      Corp EC EC1 Fran EC 1
      Fran WC WF1 Corp EC 2
      Corp EC EC2

      1. Hello!
        Your formula is working correctly. However, Excel does not work correctly with so much data. If you replace your formula with

        =IFERROR(ROWS(UNIQUE(FILTER($B$2:$B$1000, ($A$2:$A$1000=A2) * ($D$2:$D$1000=D2)))), 0)

        then it counts the number of unique values correctly.

        An alternative option for counting unique values by 2 conditions is

        =SUMPRODUCT((($A$2:$A$10000=A2) * ($D$2:$D$10000=D2)) / COUNTIFS($A$2:$A$10000, $A$2:$A$10000, $D$2:$D$10000, $D$2:$D$10000, $B$2:$B$10000, $B$2:$B$10000))

        This formula also works correctly, but if you increase the range to 100,000 rows, it returns 0.

  13. Hi,
    I have a sheet where there are multiple rows for a given text in a column, i need to pull data if there is only one unique row , if there is more than 1 row then need to display the number of rows

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(COUNTA(UNIQUE(A2:A10))>1,COUNTA(UNIQUE(A2:A10)),A2)

      I hope this will help

  14. is there any way to count the unique value based on a text string in another column ?
    In the above example, count the unique values , if the other column contain "ball" ( basketball /Volleyball )

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IFERROR(ROWS(UNIQUE(FILTER(A2:A10,ISNUMBER(FIND("ball",B2:B10,1))))), 0)

      I hope this will help

  15. Hi,
    Thank you for this! Can you help me how to implement an extra condition? In your example it would correspond to adding the criteria of Age greater then 16 but below 19. When adding an extra condition it just counts 0 which is not the right case. See below implementation:
    =IFERROR(ROWS(UNIQUE(FILTER(A2:A10, (B2:B10=F1) * (C2:C1016))), 0)

    1. Hi Petrine,

      You just need to add each condition separately, like this:
      =IFERROR(ROWS(UNIQUE(FILTER(A2:A10, (B2:B10=F1) * (C2:C10>16) * (C2:C10<19)))), 0)

  16. Tab Unique values multiple criteria, when entering a value of 10 in F2, then E5 displays a #CALC! error message as expected, but F5 displays a value of 1 as COUNTA also calculates the cell which contains a string. Using such a formula may render incorrect results, which aren't very obvious for users, without thorough checking. Unfortunately cannot use the COUNTBLANK formula to add to the existing formula and subtract records when containing a string to come to the correct result. Any other workaround?

    1. Hi Alfred,

      Thank you very much for catching this error! And my apologies for not testing the formula when the criteria are not met. In addition to Alexander's response, I can suggest the following solution:

      You can use the ROWS function to count unique entries (unlike COUNTA, it does not count error values) and IFERROR to trap all kinds of errors and replace them with 0. So, the formula in F5 would go as follows:

      =IFERROR(ROWS(UNIQUE(FILTER(A2:A10, (B2:B10=F1) * (C2:C10<F2)))), 0)

      We have updated this and a few other formulas in this tutorial. Thank you for helping me make this post a little better :)

    2. Hello Alfred!
      Thank you for pointing out this issue! We will check all the formulas in this tutorial for "non-matched" criteria and fix the erroneous ones.

      In the meantime, you can use these formulas:
      E5
      =IFERROR(UNIQUE(FILTER(A2:A10, (B2:B10=F1) * (C2:C10<F2))),"")

      F5
      =SUM(--NOT(ISERROR(UNIQUE(FILTER(A2:A10,(B2:B10=F1)*(C2:C10<F2))))))

      I hope it’ll be helpful.

  17. Good day to Ablebits Team!

    i have a column of dates with ten entries (Random future dates) for each ten different material, i wanted to have a notification that would tell me how many of those materials will expire 30 days before the dates mentioned in their respective columns. any help would do.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =SUM(--(D1:D10<(TODAY()+30)))

      Hope this is what you need.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)