How To Use Formula To Count Unique Value And Distinct Value In Excel

Leave a Comment / Formulas

This article will tell you what is the difference between unique values and distinct values in Excel, it will also tell you how to use a formula to count unique values and distinct values in Excel.

1. What Is The Difference Between Unique Value & Distinct Value In Excel?

  1. Suppose there is a column containing all the user names, and there are duplicate user names in that column.
  2. The unique value does not contain any duplicated values.
  3. The distinct value can contain both unique values and duplicate values, but for duplicated values, it will only contain once.
  4. Below are the example data columns to demonstrate the difference between unique values and duplicated values.
  5. Column A contains the original value, there are 2 cells containing text jerry li (case insensitive).
  6. Column B contains the unique value only, jerry li is not a unique value (existing twice) in column A, so it does not appear in column B.
  7. Column C contains the distinct value, so it will show the user name jerry li (duplicate value in column A) only once.
    how-to-use-formula-to-count-unique-value-and-distinct-value-in-excel-1

2. How To Use Excel Formula To Count Unique Value In Excel Example.

  1. Below is the example cell table, column A contains 5 user names.
    how-to-use-formula-to-count-unique-value-and-distinct-value-in-excel-2
  2. The user name jerry li ( case insensitive ) is duplicated, so the unique user name count number should be 3 ( Tom Ma, Tom Sun, Jackie Liu ).

2.1 Count Unique Value Numbers Use Excel COUNTIF, ISTEXT, And SUM Function Together.

  1. First using the COUNTIF function to count how many times each user name appears in column A like below.
  2. Input the formula =COUNTIF(A2:A6, A2:A6) in cell B2 and autofill other cells in column B ( you can read the article Flash Fill In Excel & How To Autofill Formula In Excel ).
  3. When you press the enter key, you will get the below result.
    how-to-use-formula-to-count-unique-value-and-distinct-value-in-excel-3
  4. Now input the formula =ISTEXT(A2:A6) in cell C2 and autofill cell range C2:C6 to get the below result.
  5. The Excel function ISTEXT will check all the cells in the cell range A2:A6, if the cell value is a text, then return TRUE, else return FALSE.
  6. This will make sure to count cells that contain text values only and filter out cells that contain other data type values.
    how-to-use-formula-to-count-unique-value-and-distinct-value-in-excel-4
  7. Input the formula =ISTEXT(A2:A6)*COUNTIF(A2:A6,A2:A6) in cell D2, if the cell value is not text value then it will return and show the number 0 in the cell.
    how-to-use-formula-to-count-unique-value-and-distinct-value-in-excel-5
  8. Input the formula =IF(ISTEXT(A3:A7)*COUNTIF(A3:A7,A3:A7)=1,1,0) in cell E2, this formula will return 1 if the related cell value in column D is 1, else return 0.
    how-to-use-formula-to-count-unique-value-and-distinct-value-in-excel-6
  9. Now, we can use the Excel function SUM to add the above numbers in column E to count out the unique user name number.
  10. Input the formula =SUM(IF(ISTEXT(A3:A7)*COUNTIF(A3:A7,A3:A7)=1,1,0)) in cell F2 and press the enter key, it will display the number 3 in cell F2.
    how-to-use-formula-to-count-unique-value-and-distinct-value-in-excel-7

3. How To Use Excel Formula To Count Distinct Value In Excel Example.

  1. In this example, column A contains the below user names.
    how-to-use-formula-to-count-unique-value-and-distinct-value-in-excel-8
  2. There are 5 user names in the above cells of column A, but 2 of them are duplicates case insensitive ( Jerry Li, jerry li ), so the distinct user name should be 4, but how to get it using the Excel formula?

3.1 Count Distinct Value Numbers Use Excel SUM And COUNTIF Function Together.

  1. We can combine the Excel SUM and COUNTIF functions to count distinct values in Excel.
  2. First using the COUNTIF function to count how many times each user name appears in column A like below. 
    COUNTIF(A2:A6, A2:A6)
  3. The above function will count the number of times each cell value ( in the range A2:A6, the function’s second parameter ) appears in the cell range A2:A6 ( the function’s first parameter ) case insensitive.
  4. For example, the first cell’s value Jerry Li appears 2 times because cell A2 and cell A3 all contain the user name jerry li case insensitive.
  5. So when you input the formula =COUNTIF(A2:A6, A2:A6) in cell B2 and press the enter key, it will display the below number array in the Excel column B.
    how-to-use-formula-to-count-unique-value-and-distinct-value-in-excel-9
  6. So the formula =1/COUNTIF(A2:A6, A2:A6) will calculate the presence percentage of each cell value in all the duplicate cell values.
  7. For example, there are 2 cells containing the text Jerry Li, so for each cell, the presence percentage of each cell is 1/2 = 0.5.
  8. Input the above formula in cell C2 and autofill cell range C2:C6 ( you can read the article Flash Fill In Excel & How To Autofill Formula In Excel ), and it will get the below data.
    how-to-use-formula-to-count-unique-value-and-distinct-value-in-excel-10
  9. Now we can use the Excel SUM function to add all the numbers in column C to count distinct name numbers in Excel cell range A2:A6.
  10. Input the formula =SUM(1/COUNTIF(A2:A6, A2:A6)) in cell D2, then you will get the number 4 in cell D2.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.