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?

- Suppose there is a column containing all the user names, and there are duplicate user names in that column.
- The unique value does not contain any duplicated values.
- And the distinct value can contain both unique values and duplicate values, but for duplicated values, it will only contain once.
- Below are the example data columns to demonstrate the difference between unique values and duplicated values.
- Column
**A**contains the original value, there are 2 cells containing text**jerry li**(case insensitive). - 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**. - Column
**C**contains the distinct value, so it will show the user name**jerry li**(duplicate value in column**A**) only once.

**A****B****C****1****User Name****Unique User Name****Distinct User Name****2**Jerry Li Tom Ma Jerry Li **3**jerry li Tom Sun Tom Ma **4**Tom Ma Jackie Liu Tom Sun **5**Tom Sun Jackie Liu **6**Jackie Liu

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

- Below is the example cell table, column
**A**contains 5 user names.

**A****1****User Name****2**Jerry Li **3**jerry li **4**Tom Ma **5**Tom Sun **6**Jackie Liu - 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.

- First using the
**COUNTIF**function to count how many times each user name appears in column**A**like below. - 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 ). - When you press the enter key, you will get the below result.

**A****B****1****User Name****Name Appear Times****2**Jerry Li 2 **3**jerry li 2 **4**Tom Ma 1 **5**Tom Sun 1 **6**Jackie Liu 1 - Now input the formula
**=ISTEXT(A2:A6)**in cell**C2**and autofill cell range**C2:C6**to get the below result. - 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**. - This will make sure to count cells that contain text values only and filter out cells that contain other data type values.

**A****B****C****1****User Name****Name Appear Times****Is Text****2**Jerry Li 2 TRUE **3**jerry li 2 TRUE **4**Tom Ma 1 TRUE **5**Tom Sun 1 TRUE **6**Jackie Liu 1 TRUE - 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.

**A****B****C****D****1****User Name****Name Appear Times****Is Text****ISTEXT(A2:A6) *****COUNTIF(A2:A6,A2:A6)****2**Jerry Li 2 TRUE 2 **3**jerry li 2 TRUE 2 **4**Tom Ma 1 TRUE 1 **5**Tom Sun 1 TRUE 1 **6**Jackie Liu 1 TRUE 1 - 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**.

**A****B****C****D****E****1****User Name****Name Appear Times****Is Text****ISTEXT(A2:A6) *****COUNTIF(A2:A6,A2:A6)****IF(ISTEXT(A3:A7)*****COUNTIF(A3:A7,A3:A7)=1,1,0)****2**Jerry Li 2 TRUE 2 0 **3**jerry li 2 TRUE 2 0 **4**Tom Ma 1 TRUE 1 1 **5**Tom Sun 1 TRUE 1 1 **6**Jackie Liu 1 TRUE 1 1 - Now, we can use the excel function
**SUM**to add the above numbers in column**E**to count out the unique user name number. - 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.**

**A****B****C****D****E****F****1****User Name****Name Appear Times****Is Text****ISTEXT(A2:A6) * COUNTIF(A2:A6,A2:A6)****IF(ISTEXT(A3:A7) * COUNTIF(A3:A7,A3:A7)=1,1,0)****Unique User Name Count Number****2**Jerry Li 2 TRUE 2 0 3 **3**jerry li 2 TRUE 2 0 **4**Tom Ma 1 TRUE 1 1 **5**Tom Sun 1 TRUE 1 1 **6**Jackie Liu 1 TRUE 1 1

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

- In this example, column
**A**contains the below user names.

**A****B****C****D****1****User Name****Name Appear Times****1/(Name Appear Times)****Distinct Name Count****2**Jerry Li **3**jerry li **4**Tom Ma **5**Tom Sun **6**Jackie Liu - 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.

- We can combine the excel
**SUM**and**COUNTIF**functions to count distinct values in excel. - First using the
**COUNTIF**function to count how many times each user name appears in column**A**like below.COUNTIF(A2:A6, A2:A6)

- 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. - 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. - 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**.

**A****B****C****D****1****User Name****Name Appear Times****1/(Name Appear Times)****Distinct Name Count****2**Jerry Li 2 **3**jerry li 2 **4**Tom Ma 1 **5**Tom Sun 1 **6**Jackie Liu 1 - So the formula
**=1/COUNTIF(A2:A6, A2:A6)Â**will calculate the presence percentage of each cell value in all the duplicate cell values. - 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**. - 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.

**A****B****C****D****1****User Name****Name Appear Times****1/(Name Appear Times)****Distinct Name Count****2**Jerry Li 2 0.5 4 **3**jerry li 2 0.5 **4**Tom Ma 1 1 **5**Tom Sun 1 1 **6**Jackie Liu 1 1 - 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**. - Input the formula
**=SUM(1/COUNTIF(A2:A6, A2:A6))**in cell**D2**, then you will get the number**4**in cell**D2**.