Lets say that you have a column with values, and you need to find out what distinctunique values are in there.Go to Data Ribbon menu - Sort Filter section and click the Advanced button.
![]()
Unique values from source list are copied to new list starting at cell selected in Copy to property of Advanced filter dialog. ![]() This means that it keeps expanding as you go down the column. With a basic understanding and a little drag and drop, you can get a bucket-load of work done in a few seconds. While a lot can be done with a few clicks in Pivot Tables, there are some things that would need a few extra steps or a little bit of work around. And one such thing is to count distinct values in a Pivot Table. Count Distinct Values Excel 2010 How To Count DistinctIn this tutorial, I will show you how to count distinct values as well as Unique Values in an Excel Pivot table. This Tutorial Covers: Distinct Count Vs Unique Count Count Distinct Values in Excel Pivot Table Adding a Helper Column in the Dataset Add Data to Data Model and Summarize Using Distinct Count Count Unique Values (not distinct values) in a Pivot Table But before I jump into how to count distinct values, its important to understand the difference between distinct count and unique count Distinct Count Vs Unique Count While these may seem like the same thing, its not. Below is an example where there is a dataset of names and I have listed unique and distinct names separately. This means that all the names that repeat and have duplicates are not unique. Unique names are listed in column C in the above dataset Distinct valuesnames are those that occur at least once in the dataset. So if a name appears three times, its still counted as one distinct name. This can be achieved by removing the duplicate valuesnames and keeping all the distinct ones. Based on what I have seen, most of the times when people say that they want to get the unique count in a Pivot Table, they actually mean distinct count, which is what I am covering in this tutorial. Count Distinct Values Excel 2010 Download The ExampleCount Distinct Values in Excel Pivot Table Suppose you have the sales data as shown below: Click here to download the example file and follow along With the above dataset, lets say that you want to find the answer to the following questions: How many sales rep are there in each region (which is nothing but the distinct count of sales reps in each region) How many sales rep sold the printer in 2020 While Pivot Tables can instantly summarize the data with a few clicks, to get the count of distinct values, you will need to take a few more steps. If youre using Excel 2013 or versions after that, there is an inbuilt functionality in Pivot Table that quickly gives you the distinct count. ![]() The following two methods are covered in this tutorial: Adding a helper column in the original data set to count unique values (works in all versions). Adding the data to a data model and using Distinct Count option (available in Excel 2013 and versions after that). There is a third method which Roger shows in this article (which he calls the Pivot the Pivot Table method). Lets get started Adding a Helper Column in the Dataset Note: If youre using Excel 2013 and higher versions, skip this method and move to the next one (as it uses an inbuilt Pivot Table functionality Distinct Count ). This is an easy way to count distinct values in the Pivot Table as you only need to add a helper column to the source data. Once you have added a helper column, you can then use this new data set to calculate the distinct count. While this is an easy workaround, there are some drawbacks to this method (covered later in this tutorial). Let me first show you how to add a helper column and get a distinct count. Suppose I have the data set as shown below: Add the following formula in Column F and apply it for all the cells that have data in the adjacent columns. IF(COUNTIFS(C2:C2,C2,B2:B2,B2)1,0,1) The above formula uses the COUNTIFS function to count the number of times a name appears in the given region.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |