How to Count Distinct Values in Excel: 5 Simple Methods

The conditional counting function in Excel will help you with that

4 min. read

Updated onOctober 4, 2024

updated onOctober 4, 2024

Share this article

Improve this guide

Read our disclosure page to find out how can you help Windows Report sustain the editorial teamRead more

To count distinct values in Excel, you need to add the unique values with the first of the duplicate value. Although it sounds complicated, you will see that there are a few simple formulas to apply to your table.

How do I count distinct Excel values?

1. Using the COUNTIF function

This is the most simple method of counting distinct values in Excel. If you have empty cells in the column, you need to add an IF condition to exclude them. Example:=SUM(IF(A2:A10<>””,1/COUNTIF(A2:A10, A2:A10), 0)). Again, replace the A2:A10 with the range you actually want to count.

If the values in your range are text, apply the following formula and replace the A2:A10 range with your actual interval:=SUM(IF(ISTEXT(A2:A10),1/COUNTIF(A2:A10, A2:A10),""))

2. Counting distinct case sensitive values

This method is ideal when you have text values and you want to consider them different based on case sensitivity. This way, two values written as Two and TWO will be considered distinct.

3. Using the UNIQUE Function (Excel 365 and Excel 2019)

The COUNTA function also excludes the blank cells in your range so you won’t need to worry about that.

4. Using a Pivot Table

If you change the values in your range, make sure to refresh the Pivot Table. To do that, click theRefreshbutton on theAnalyzetab, in theDatagroup.

5. Using Power Query

6. Using Advanced Filters

These methods should help you count distinct values in Excel efficiently. If you didn’t know, you may also use natural language queries to retrieve data from an Excel table. Just go through our guide onhow to use Ideas in Excelto find out how to do that.

You might also be interested in reading our guide onhow to reset Excel to default settings. Moreover, you can learn how to deal with thegreen triangle errorindicator in Excel.

If you have any specific questions or need further assistance, feel free to ask in the comments below.

How to Count Distinct Values in Excel: 5 Simple Methods#

How do I count distinct Excel values?#

1. Using the COUNTIF function#

2. Counting distinct case sensitive values#

3. Using the UNIQUE Function (Excel 365 and Excel 2019)#

4. Using a Pivot Table#

5. Using Power Query#

6. Using Advanced Filters#