How to Count Distinct Values in Excel: 5 Simple Methods

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

1. Select the range where you want to count distinct values.
2. Enter the formula: =SUM(1/COUNTIF(A2:A10, A2:A10))
3. Make sure to replace A2:A10 with your actual range.
4. Press Ctrl + Shift + Enter to make it an array formula. Excel will add curly braces {} around the formula.

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

1. Select the range where you want to count distinct values.
2. Enter the following formula to determine the distinct case sensitive values: =IF(SUM((--EXACT($A$2:$A2,$A2)))=1,"Distinct","")
3. Now, apply the formula to count the distinct values: =COUNTIF(B2:B10, "distinct")
4. Press Ctrl + Shift + Enter after each array formula to validate it.

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)

1. Select a cell where you want the result.
2. Enter the formula: =COUNTA(UNIQUE(A2:A10))
3. Replace A2:A10 with your actual range.
4. Press Enter. This will count the number of distinct values in the range.

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

1. Select your data range.
2. Go to the Insert tab and click on PivotTable.
3. Choose where you want the PivotTable report to be placed.
4. Drag the field you want to count into the Rows area.
5. Drag the same field into the Values area.
6. Click on the drop-down arrow next to the field in the Values area and select Value Field Settings.
7. Choose Distinct Count and click OK.

If you change the values in your range, make sure to refresh the Pivot Table. To do that, click the Refresh button on the Analyze tab, in the Data group.

5. Using Power Query

1. Select your data range.
2. Go to the Data tab and click on From Table/Range.
3. In the Power Query Editor, select the column you want to count distinct values for.
4. Go to the Transform tab and click on Group By.
5. In the Group By dialog box, select Advanced.
6. Add a new aggregation and set it to Count Rows.
7. Click OK and then Close & Load.

6. Using Advanced Filters

1. Select the column you want to count distinct values in.
2. Go to the Data tab and click on Advanced in the Sort & Filter group.
3. Select Copy to another location.
4. Specify the List range and Copy to range.
5. Check Unique records only and click OK.
6. Use the COUNTA function as in Solution 2 to count the unique values in the new range.

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 on how to use Ideas in Excel to find out how to do that.

You might also be interested in reading our guide on how to reset Excel to default settings.

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