Comments on: How to count unique values in Excel

In this tutorial, you will learn how to count unique values in Excel with formulas, and how to get an automatic count of distinct values in a pivot table. We will also discuss a number of formula examples for counting unique names, texts, numbers, cased-sensitive unique values, and more. Continue reading →

Comments page 2. Total comments: 126

  1. Hi I have a tables with dates in column a, projects in column B, and employee names in column c. The employee names repeat on the table and so do the project.

    I have a another table with the project names removed with duplicates. I need help with a formula that would count how many employees worked on that job, leaving out duplicates, based on the project name in the column beside it. Any suggestions?

  2. Thank you, this is super helpful! I am trying to count distinct values in a column using a wildcard value. In other words, I want to distinctly count all the values that start with "abc" in a column and separately, I want to also distinctly count all the values that start with "xyz" in the same column. What is the "if" statement that I can use in front of the 1/COUNTIF part of the formula?

      1. The solution for me was the following:

        SUM(IF(ISNUMBER(SEARCH(A11,)),1/COUNTIF(,),0))

        where A11 is the search text "abc" in my question above.

  3. Dear friends,
    I am trying to do a distinct count of Purchase orders numbers, excluding blanks in Column B. This I can do with the above help, but I am struggling to find a way to add an extra condition, that the distinct count should be only from 1 requisition type from the 5 types in column A.
    A little nudge in the right direction would save my life!

    Thank you so much, I appreciate so much your wonderful explanations.

      1. Thank you so much for your reply.
        I have tried to look at that article but I am not sure how I could use it to find a distinct count of order numbers and not unique order numbers. I am trying to get the results show in column D. So sorry to ask again for help, but I really do appreciate it!

        Column A Column B Column C Column D
        Requisition type: Order Number: Distinct Orders by Req Type:
        Purchase Req 500. Purchase Req 2
        Purchase Req. 500. Inventory Replenishment 2
        Purchase Req. 501
        Inventory Replenishment 502
        Inventory Replenishment 502
        Inventory Replenishment 503
        Inventory Replenishment 503

  4. Hello,

    Is there a way if i can make a sheet about late commers ( if i check his name out i can seeee how many time he is late )?

    with lookups

    If you know what a mean

    kind regards

  5. Hello Friends, I need to find the top 3 fruits and its total count using a single formula without pivot/list.

    Fruits

    Banana
    Apple
    Mango
    Orange
    Orange
    Mango
    Orange
    Mango
    Orange
    Mango
    Apple
    Orange
    Mango
    Apple
    Orange
    Mango
    Banana
    Apple
    Mango
    Orange
    Orange
    Mango
    Orange
    Mango

    1. Yes! you Can by using assigned function

      =CONCATENATE("Banana = ",COUNTIF($B$4:$B$27,"Banana"), ", Apple = ",COUNTIF($B$4:$B$27,"Apple"), ", Mango = ",COUNTIF($B$4:$B$27,"Mango"),", Orange = ",COUNTIF($B$4:$B$27,"Orange"))

      1. Thank you. But Im using MS Office 2019 version and unique function is not available. Can you please confirm the other options to list the unique values

  6. Hi. Is there any formula to count the distinct numbers or texts across multiple spreadsheets? There are some duplicates across different spreadsheets. thank u so much.

    1. Hello!
      I don't think it's possible to find duplicates in two tables with a formula. We have a tool that can solve your task in a couple of clicks - Compare Tables tool. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

  7. I have an autofiltered column with different week numbers from 1 to 52 and from this I want to calculate the distinct number of filtered week numbers. Let's say I have excluded all week numbers higher than 5 and have the followning rows shown:

    1
    1
    2
    3
    3
    3
    5

    I have tried to calculate the number of distinct weeks (4) with use of this formula which was entered with Ctrl-Shift-Enter:

    =SUM(HVIS(FREKVENS(HVIS(SUBTOTAL(3;FORSKYDNING($D$9;RÆKKE($D$9:$D$2000)-RÆKKE($D$9);;1));HVIS($D$9:$D$2000"";SAMMENLIGN($D$9:$D$2000;$D$9:$D$2000&"";0)));RÆKKE($D$9:$D$2000)-RÆKKE($D$9)+1);1))

    Unfortunately, it seems like this formula only work with text values and not with week number even I have formatted the cells as text.

    Therefore, I will appreciate to hear from you what I can do.

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
      To count distinct values you can use this array formula:

      =SUM(IF(A1:A20<>"",1/COUNTIF(A1:A20, A1:A20), 0))

      1. Thank you for the tip.

        I have tried the formula and it works fine. But when I use it in a column with autofilter, all rows are included in the calculation - even the rows that are not included in the filter.
        Is it possible to adjust the formula so that only the filtered rows are included in the calculation, as it e.g. done in Subtotal (9; range)?

        In my example, the formula should show 3 if week 5 is not included in the filter.

        1. Hello!
          Unfortunately, the SUBTOTAL and AGGREGATE functions do not work with arrays. They only work with cell ranges. Therefore, you can write on a separate column the array formula (for example, in cell L1)

          =IF(SUM((EXACT($A1:$A$1,$A1)*($A1<>"")))=1,1,"")

          This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter. After that, you can copy this formula down along the column.
          To count distinct values that are not hidden by the filter, use the formula

          =AGGREGATE(9,5,L1:L50)

          I hope it’ll be helpful.

  8. Hello,

    Below is sample data for a limo service company. My goal is to write a formula to count the number of distinct drivers that drove on a given day with certain criteria (see example criteria below)

    Example Criteria: Confirmed: Yes, Paid: Yes, Size purchased: Sedan

    We can pretend my data is A2 to E13 below:
    Date Confirmed? Paid? Size purchased Driver
    1/1/2022 Yes Yes Sedan Bob
    1/1/2022 Yes Yes SUV Joe
    1/1/2022 No No SUV Joe
    1/1/2022 Yes Yes Sedan Mike
    1/1/2022 Yes Yes Sedan Matt
    1/1/2022 Yes Yes SUV Joe
    1/1/2022 Yes No Sedan Bob
    1/2/2022 No No Sedan Bob
    1/2/2022 Yes Yes Sedan Bob
    1/2/2022 Yes Yes SUV Joe
    1/2/2022 Yes No SUV Steve
    1/3/2022 Yes No Sedan Bob
    1/3/2022 Yes Yes Sedan Bob

    The answer based on the below would be 3 for 1/1/2022 (Bob, Joe and Matt). Bob drove two different customers on 1/1/2022 and Joe and Matt each drove 1, but since I'm looking for distinct count, I only want Bob's name once. I thought I had this solved in a pivot table, but it's not producing the correct answers and a formula would be much easier for the particular report I am producing. Thank you in advance!

    1. Hello!
      Your data example does not match the description. You can try the formula

      =COUNTIFS(B2:B14,"Yes",C2:C14,"Yes",D2:D14,"Sedan",A2:A14,"="&"01.01.22")

      It gives the result - 3.
      If you want to count the number of unique values in column E, use this formula

      =COUNTA(UNIQUE(FILTER(E2:E14,(B2:B14="Yes")* (C2:C14="Yes")*(D2:D14="Sedan")* (A2:A14=DATEVALUE("01.01.22")))))

      You can find detailed instructions and examples in this article: How to count unique values in Excel with criteria.
      I hope it’ll be helpful.

      1. Is it possible to achieve this in excel 2016?

        I have this data and need to do a distinct count of company name where column B is NOT blank

        Company Criteria
        Company A Yes
        Company B Yes
        Company C
        Company D Yes
        Company A
        Company C Yes
        Company A Yes
        Company B

    2. Correction: the correct names would be Bob, Mike and Matt (not Joe since Joe's in a SUV). Apologies

  9. Good morning,

    I'm working on the EEO-4 data report and trying to pull the data without having to manually count how many employees fit the criteria I'm being asked to report on. I've tried using a pivot table and the countifs function but cant seem to quit get it to work. I need to look at 1. Job category, ethnicity, gender and salary range and list how many employees fall under that category.

    Ex: How many male technicians make between $55 - $69,999?

    The report has a salary band that is making it difficult for me to group the salaries by lets say $5k, $10K, etc. Range I need to use is below.
    $0.1 - $15.9
    $16.0 - $19.9
    $20.0 - $24.9
    $25.0 - $32.9
    $33.0 - $42.9
    $43.0 - $54.9
    $55.0 - $69.9
    $70.0 PLUS

    How can I use a pivot table or formula/both to quickly fill in this chart? Do I need to change the format of my spreadsheet in a way that is easier to use a formula? There are multiple job categories, multiple salary ranges, and multiple ethnicities. My excel spreadsheet has the exact job category, salary and ethnicity for each employee.

  10. Hi. How to count the number of dates in the cell for a specific name in another cell?
    e.g:

    SALESPERSON DATE TO CUSTOMER TOTAL NO. OF DATES THE SPECIFIC SALESPERSON SEND OUT
    cindy 11/11/2021 =(TOTAL NO. OF DATES CINDY SENT OUT)
    cindy 15/11/2021
    cindy 18/11/2021
    cindy 21/11/2021
    alan 1/11/2021 =(TOTAL NO. OF DATES ALAN SENT OUT)
    alan 2/11/2021
    alan 3/11/2021
    alan 4/11/2021
    shane 17/11/2021 =(TOTAL NO. OF DATES SHANE SENT OUT)
    shane 18/11/2021

  11. Could you pls advise how to calculate the following with easiest method.

    A B C D E F G H I
    1 X IX III II V I V VII II
    2 IX VIII I VI IV X III II V
    3 IX VIII I VI IV X III II V
    4 V I X IX I II V V VI
    5 VII II X II III IV V VIII I
    6 VIII III IX IX I II VII V VII
    Actually I want to calculate how many period a teacher is taking in each class in a week.
    Hope your reply soon.

  12. I am still struggling with a concise way to count unique values in a column based on the values in another column. For example, let's say I want to count the unique occurrences of a name in Column A based on Column B containing the keyword "count". Say that this is my data in A1:B6:

    Robert count
    Mitch
    David
    Jeremy count
    Robert count
    David count

    The desired output is 3, because there are 3 unique names marked "count". However, the array formula ={SUM(IF(B1:B6 = "count", 1/COUNTIF(A1:A6, A1:A6), 0))} returns 2.5, because the unmarked David is included in the COUNTIF denominator. What would be the right formula to do what I'm trying to do?

    1. Hello!
      You can learn more about counting unique values with criteria in Excel in this article on our blog.

      =IFERROR(ROWS(UNIQUE(FILTER(A2:A10,B2:B10="count"))), 0)

      This should solve your task.

  13. Hi
    The formula you give
    =IF(A1 < TODAY(),"", A1-TODAY())
    is not showing the minus number of day or days
    and this below
    =IF(ISBLANK(A1),"",A1-TODAY())
    is working but I want the result cell will show with the text Day or Days including the Numbers. For example, 2 Days, 1 Day, -1Day, -3 Days & I want Zero Day will not show, or the cell (Zero Day) will blank.

    Please also suggest how to share my excel file with you.

    My second question is the subtraction of two dates comes zero (A1-A2=0) I want the cell will show the text "Same Day" and if A1 & A2 is blank result will show also blank, hope you understand, thanks in advance.

    Regards
    Sazedul Munna

      1. Hi
        I tried many ways but I can't get my expected result. I can't get the number including text Day or Days, For example, 2 Days, 1 Day, -1Day, -3 Days & I want Zero Day will not show, or the cell (Zero Day) will blank. I would like to share my excel file with you to solve these.

        Thanks
        Sazedul Munna

  14. Hello!

    Suppose if in column A text is given i want duplicate will give same Previous No and unique text will be sum of highest no

    eg.
    C-A C-B
    XYZ - 1
    ABC - 2
    CDE - 3
    XYZ - 1
    DEF - 4
    ABC - 2
    DEF - 4

    *Column-A is text & Column B i want
    Can you pls help me t

    1. Hello!
      Your list should start in cell A2. Suppose your data are in column A, please try to enter the following formula in cell B2 and then copy it down along the column:

      =IF(COUNTIF($A$1:A2,A2)=1, MAX($B$1:B1)+1, INDEX($B$1:B1,MATCH(A2,$A$1:A2,0)))

      This should solve your task.

  15. Hello,
    I want unique count of patient by location. I know how to do this using Pivot, but i don't know how to do this with excel formulas.

  16. I've a requirement which i'm unable to figure out.
    There are dates in a column, names in another column, product IDs in another column.
    Names & product ID's would repeat.
    How to create a crosstab report with names as colum header, month as row header & count of distinct product IDs as data fulfilling the headers.
    Thanks in advance.. :)

  17. I have a simple problem, but I cannot get my head round how to do it, and not found an answer on the web.

    I have a column of multiple entries of 1000 mixed up products. Each product has a column with a quantity.

    So OK its easy to sort products, using inbuilt 'sort' on a column.

    The next bit I fail on. I have tried a lot of variants of countif.

    A - 2
    A - 4
    B - 5
    B - 6

    I want a result as follows

    A - 6
    B - 11

    Any ideas please.

  18. What if some of my text that I want to distinct count are more than 255 characters long? The limit of the COUNTIF function is that it processes only characters that are less than 255 characters, otherwise the formula would return a #VALUE!. Is there a workaround for this problem? Hope you can help.

    1. Hello!
      The SEARCH function does not have the same restrictions as the COUNTIF function. If I understand the problem correctly, you can use the formula to count matches in long text:

      =SUM(--ISNUMBER(SEARCH("Text to find",A2:A100,1)))

      I hope my advice will help you solve your task.

      1. Thanks for the reply Alexander. But I need a formula that distinct counts a column of text wherein some texts are more than 255 characters long. The formula that you gave does not seem to give the right total count.

        1. Hello!
          The formula I sent to you was created based on the description you provided in your first request. However, as far as I can see from your second comment, your task is now different from the original one.
          This formula will return TRUE if there is a cell longer than 250 characters in the column.

          =IF(SUM(--(LEN(A1:A1000)>250))>0,TRUE)

  19. 2020 January Nick
    2020 January Nick
    2020 January Nick
    2020 January Jim
    2020 February Adam
    2020 February Adam
    2020 February Adam
    2020 February Adam
    2020 February Nick
    2020 February Jim

    i apologize that i didnt clearly state what i was looking for. when i type in the formula that you suggested, it comes back with 0. in the above data, i am trying to figure how many different names or people showed up in January 2020, how many in February 2020, etc. So for January 2020, the result should be 2. and for February 2020, the result 3.

    1. Hello!
      Your data changes all the time. For these you can use the formula

      =SUMPRODUCT((A1:A10&B1:B10="2020"&"January")/COUNTIFS(B1:B10,B1:B10,C1:C10,C1:C10))

      Hope this is what you need.

      1. Dear Alexander

        what is the formula if i use range data from A1 until C100, which is the cell is fill until A10:C10 for this moment.
        when tomorrow i will fill the data cell from A11:C12
        And the day after tomorrw i will fill data cell from A13:C15
        Just like the example for this moment the cell fill with data january and february. and tomorrow i fill with march
        So i dont need to change range data because the formula has range wider which is until 100 rows.
        Thank for your answer.

        1. Dear Alexander

          column A11 until C100 is still empty cell
          i will not fill it in the same day. i fill it once a day.
          the formula can read empty cell

          thanks

  20. 2020 Jan Steve
    2019 Jan Mike
    2020 Feb John
    2018 Mar Nick
    2020 Feb Mike
    2020 Feb Mike
    2019 Jan Steve

    that formula doesnt seem to be working. The above data is a sample of what i am working with. i am trying to figure out how many different people showed up on a particular year and month. There are going to be many duplicate first names that i only want counted one time. hope you can help!

    1. Hello!
      I wrote this formula based on the description you provided in your original comment. Please note that if you’d provided me with the precise and detailed description of your task from the very beginning, I’d have created a different formula that would have worked for your case.
      However, if you write that the formula does not work, but do not say what exactly does not work, I cannot help you. I will not guess your desires. What result do you want to get?
      For your data, you can replace the month number 2 with "Feb". I think it's not difficult.

  21. i have 3 columns of data. The first column is the Year, the 2nd column is the month, and the 3rd column is a list of First names. im trying to create a formula that would count for example how many DIFFERENT names appear in February 2019. Can someone please help??

    1. Hello!
      The formula below will do the trick for you:

      =SUM(IF(COUNTIFS(C1:C10,C1:C10,B1:B10,2,A1:A10,2019)=1,1,0))

      The formula counts the number of unique rows that match the conditions.

  22. Hi,
    This was very useful, but I am having some issues:
    1) I have column A as the month (1=Jan, 1=Feb, etc), and column B as the Client. When counting TOTAL distinct clients for the entire year (ie all months), how can I adjust the formula so it does not count cell B1 (the header/name of column), and updates dynamically as more data is added to the raw data sheet?
    2) How can I adjust this formula so I can find # distinct clients broken down by month, updated dynamically as more data is added?

    Thank you!

    1. Hello Olivia!
      To count the number of unique values in column B, use the instructions provided earlier in this article. Specify the counting range large, for example B2: B9999
      To calculate the number of unique customers per month, use the additional condition - the number of the month.
      If the month number = 2, then you can use something like this formula

      =SUM(IF(ISTEXT(B2:B100)*(--IF(A2:A100=1,1,0))*COUNTIF(B2:B100,B2:B100)=1,1,0))

      I hope it’ll be helpful.

  23. Hello,
    I'm creating a data table with that shows the count of people in each expertise level. The count is created by a 'COUNTIFS' formula. Formula looks as follow:
    =(IF(COUNTIFS(SkillsetData!B:B,IndustryExpert_ORTEMS_CSVREADY!B2,SkillsetData!CP:CP,">0",SkillsetData!AD:AD,IndustryExpert_ORTEMS_CSVREADY!$L$2)=0,"",(COUNTIFS(SkillsetData!B:B,IndustryExpert_ORTEMS_CSVREADY!B2,SkillsetData!CP:CP,">0",SkillsetData!AD:AD,IndustryExpert_ORTEMS_CSVREADY!$L$2))))

    The next challenge is to base the count on unique values. I have one column called "name' (A) with duplicates in it. Now I am looking for a solution to integrate a formula in the existing formula that only counts the unique 'name' from column A before starting to look at the other criteria in the COUNTIFS formula.

    I really appreciate your help.
    Have a nice day!

    1. Hello Alexander,
      Thank you for your reply. I did some more research today and I have found a way to create the result that I was looking for. I needed to define per row if there are any duplicated based on two columns (without removing the data). I found a nice trick with a reverse COUNTIFS formula. Starting from the bottom and dragging it up.

      Anyway thanks for your reply and offer to help!
      Have a nice day.

  24. Good day,
    could you support me with a solution for a such output : the result should be at the end of the list (the last cell), I have huge list of columns and I need to know the distribution of each one, without passing through Pivot table:

    Item
    0
    0
    11
    11
    11
    11
    4
    4
    4
    6
    6
    6
    6
    0(2),11(4),4(3),6(4)

    Thank you!

  25. I have a spredsheet having column A with repeated names. The an other column B contains the month. One more column C contains some text like fresh/renewal/enhancement. I need to count unique text or names entered in Column A, with criteria if month in column B is "MAY" and Column C contains text "Fresh". the formula should works If there is blank cell also and if we added the data frequently at below.

    1. Hello!
      If I understand your task correctly, to calculate the number of unique values by conditions, use the formula:

      =SUMPRODUCT(--(B2:B17="MAY"), --(C2:C17="Fresh"), --($A$2:$A$17=A2))

      I hope this will help

  26. I want to count the number of unique values based on the string in the cell and the format as my data is distingquished by format. So if I have 50 SAC 25 blue and 43 green. It would be counted as 2 in the occurances.

  27. None of this works. Rubbish article.

  28. Hi,

    I have sought to use the following formula:

    =SUM(IF(COUNTIF(A2:A10,A2:A10)=1,1,0))

    No matter how implemented it returns 0. I have even created some simple new text data in a new column (Jim, Bob, Jim, Sally, Joe, Bob, Sally, Joe, Jim) in a new test spreadsheet, and the same happens.

    I can only conjecture that the reason for this is that COUNTIF(RNG1,RNG1) returns an array, not a single number, and so produces 0?

    The concept seems so simple, but I have spent that last few hours trying to resolve this maddening issue.

    Help! Please!

    B

    1. Hello,

      This is an array formula, so be sure to press Ctrl + Shift + Enter to complete it properly. Once you do this, Excel will automatically enclose the formula in {curly braces}. Typing the curly braces manually won't work.

      Hope it will help you!

  29. First - thank you so much for this site. Very helpful.

    I have a spreadsheet with a large number of distinct account numbers. Each time an account number appears on the spreadsheet, whether duplicate or unique, it represents a different transaction with a value for each transaction. There are 3 columns, (A) for acct. #; (B) for transaction 1; and (C) for interest on transaction 1. I would like to get a list of distinct account numbers with the sum of the values for each distinct acct. number. (At the end, the list would contain a column for: (A) Distinct Acct.#; and, (B) sum or total transaction value for all values for each acct#; and (C) total or sum of interest for each acct#.

    1. Hello Paul,

      Thank you very much for your feedback, we're happy to hear you find our site helpful.

      It sounds like the Subtotal option in Excel will do what you need:
      - Select your records and go to Data tab
      - Click on Subtotal and choose the following options:
      - At each change in - column with the account number, use function - SUM, add subtotal to - select the columns with the values you want to sum.
      - Click OK

      I hope this helps.

      1. Yes - absolutely worked. Wonderful. Thank you!!

  30. How would I count distinct dates in a selection of cells? I tried changing the dates to number format and then using =SUM(IF(ISNUMBER(range),1/COUNTIF(range, range),"")), but I got an incorrect answer with an error saying that the formula "omits adjacent cells".

    1. I too, got an incorrect value when using this formula to count unique dates. No error message, but it gave me a result of less than 1.

  31. Hi,

    I don't see the item "Add this data to the data model" in the Create Pivot Table window. How come? I am using Excel 2010.

    1. Hi Fab,

      Regrettably, this option is not available in Excel 2010. It was introduced in Excel 2013, and also exists in Excel 2016. In earlier Excel versions, you can count unique values using formulas as demonstrated in the first parts of this tutorial.

  32. Hi,
    Would you please help me on this scenario. I have a spreadsheet where column a shows patient name. Column b shows type of service. Column a have duplicate patient names since some patient gets more than one services. Now I need to identify( count) how many patients were receiving more than one services. Would you help me with the fomular? Or a way to do that. Thank you very much.

    1. Hello Fiona,

      You can do it in the following way:

      1. Add the following formula, say in column C, to identify duplicate names:
      =IF(COUNTIF($A$2:$A2, $A2)>1, "Duplicate", "")

      Where A2 is the first cell in the Patient Names column.

      2. Apply Excel's filter, filter out the unique name so that only duplicate names are visible, and copy those duplicate names to another sheet.

      3. In that other sheet, use the following array formula to count distinct names (unique +1st duplicate occurrences). Remember to press Ctrl+Shift+Enter to complete the formula:
      =SUM(1/COUNTIF(A2:A8,A2:A8))

      Where A2 is the first and A8 is the last cell.

      I've create a quick example for you, and you can download it here.

      1. After filtering out duplicates, what you have are unique names. So, applying CountA(A2:A8) would give the count of those names. Why do we need to apply the array formula as mentioned by you? Any specific reason?

        1. Hi John,

          Fiona's task was to count how many patients were receiving more than one services. For this, we needed to get a list of duplicates names, not unique names, and then count how many different (distinct) names appear in that list. I've re-worded point 2 in my previous comment, and hope now it makes more sense.

          For example, after filtering out the unique names, we get the following list of duplicate names: Peter, Maria, Peter, Rob, Maria. And now, we need an array formula mentioned above to count how many different names appear in this list (3 names). You can find the detailed explanation of the formula in How the distinct formula works.

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 :)