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

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

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

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

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

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

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

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

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

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

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

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