It’s easy to Count Distinct values in Excel using the COUNTIF formula or by selecting DISTINCT COUNT in a Pivot Table. The option is turned off by default when creating a Pivot Table and is only available when adding Pivot Table data to an Excel Data Model. We’ll look at multiple methods to perform distinct counts in Excel and explain how they can be used to count the unique values in any given list or column.
If you’re unfamiliar with the different COUNT functions in Excel, we’ll explain each one of them for you.
Then we’ll look at how to use a Pivot Table to count the distinct or unique values in a list, along with how to troubleshoot and fix if the option has been greyed out or you don’t see it available.
Let’s Jump in!
Table of Contents
What is a Distinct Count?
Whether you’re an urban planner, social media manager, retail store owner, or analyst, understanding the distinct elements of your dataset can be extremely valuable. Knowing the uniqueness of the values in your dataset can help you make better informed decisions.
A distinct count is a statistical technique used to identify the number of unique items in a given dataset. Unlike a simple count, which tallies up all items regardless of repetition. A distinct count only counts each unique item once.
While tools like SQL databases, and Power BI offer clear ways to Distinct Count, it’s a little more difficult in Excel.
Luckily, you can re-create the functionality using a combination of COUNT and COUNTIF formulas.
Let’s take a look!
Excel COUNT and COUNTIF Functions Explained
There are two formulas for this method you will need to be aware of, COUNT and COUNTIF. Both are similar in purpose but have slight variations. A COUNT will count all values in a column, while a COUNTIF will count all values in a column based on a condition.
The Excel COUNT Function
The COUNT function in Excel is one of the simplest and most commonly used functions to count cells containing numerical data. The formula for the Count function is:
=COUNT(Range)
Here, range refers to the range of cells that you want to count.
It would look something like this: =COUNT(A:A) to count all of the values in column A
The Excel COUNTIF Function
While the Count function is excellent for counting numbers, COUNTIF allows for more specific counting, based on a condition. The formula for the COUNTIF function is:
=COUNT(Range, Criteria)
In this formula, Range refers to the cells you want to count, and Criteria specifies the condition the cells need to meet to be counted.
It would look something like this: =COUNTIF(A:A , “1”) to count all of the values in column A greater than 1.
How to Count Distinct in Excel using COUNTIF
To Count Distinct in Excel, create a column to perform a simple count. This column will tell you how many times a value appears in a column. Then use a COUNTIF to count how many times the original count column is equal to 1. By splitting it into two steps and using a helper column, you can avoid complex formulas and make the workbook easier to follow.
Step 1.) Use a COUNTIF Formula to Determine Frequency of a Value
In the screenshot below, we use a COUNTIF formula to check how many times the value in a row of our column appears in the entire column.
Then we fill the COUNTIF formula down to the bottom of the list, which creates a new column that shows us how many times the value appears in the entire column.
Step 2.) COUNTIF to Conditionally Count the First Column
In this step, we use the COUNTIF formula again, but we count column E to determine all of the values that have a count of 1.
By splitting this step into two sections, we can quickly identify which rows are unique within our column. For added detail we could also add a filter to our titles and filter the count column to view only our unique rows.
Step 3.) Hide the Helper Column
To clean up the formatting of our Excel report, we can hide the helper column by right clicking on the letter of the column and select Hide. The data remains in the dataset, but will not be visible to other people who open the report.
How to Count Distinct in an Excel Pivot Table
To Count Distinct using an Excel Pivot table, select import data to model when creating a pivot table, add fields to the applicable sections of the pivot table, and select Summarize Values as > Distinct Count. This option is only available if data was added to the model at the time of creating the pivot table. We’ll look at these steps in more detail.
Step 1.) Insert a new pivot table, after selecting a range of data to add to the pivot table. Do this by navigating to Insert, Pivot Table, From Table/Range.
Step 2.) Select where you want to add the new pivot table. In our example we will create a new worksheet.
Important: Make sure that you select Add this data to the Data Model!
If you do not check this box, you will not be able to distinct count in a pivot table.
Step 3.) Setup the new pivot table the way that you want to present data. On the values column it will default to SUM. Modify it to Distinct Count, by right clicking on the column, go to Summarize Values By, then select Distinct Count from the bottom of the list.
Distinct Count will only show the count of Unique Values in the new pivot table. You can also add the same field multiple times into a pivot table with different aggregation methods. For example, you could have columns for both the simple count of values and the Distinct Count of values.
The following video explains in detail how to perform a Distinct Count using a Pivot Table in Excel.
What Does “Add to Data Model” Do in an Excel Pivot Table?
The “Add to Data Model” option in Excel’s PivotTable feature enables advanced data analysis by leveraging Power Pivot capabilities. One of its key benefits is the ability to perform a distinct count easily, something that’s not readily available in standard PivotTables. It also allows for more complex relationships between multiple tables and can handle large datasets more efficiently.
What to Do if Distinct Count is Greyed out or Missing in an Excel Pivot Table
If you right click on pivot table values and the option of Summary Values by Distinct Count is not available, it is due to the pivot table data not being added to the data model. Delete the pivot table, re-select the range that you want. Then go to Insert, Pivot Table. Make sure that you check “Add this data to the Data Model” at the bottom of the new Pivot Table options.
Conclusion
Setting up a distinct count in Excel requires a couple of additional steps, but can be done very easily once you get the hang of it. While there are several methods to distinct count within in an Excel spreadsheet we like using the two step process using the COUNTIF formula. It’s easy to trace back, and you only need to know a single formula that has many other uses instead of trying to get overly complicated.
Another way to count distinct values in Excel is using a Pivot Table. As long as you add the pivot table data to the data model it enables a lot of features that are only available with Power Pivot, with distinct count being one of the most common ones.
If you forget to add pivot table data to a data model, the distinct count option will not be available. Delete the pivot table, and create a new one with this option enabled.