Click on Pivot Table (or use the keyboard shortcut – ALT + N + V).Now that we have modified the source data, we can use this to create a Pivot Table and use the helper column to get the distinct count of the sales rep in each region. This makes sure that only distinct names are counted and not the repeats.īelow is how your dataset would look like when you have added the helper column. Since we only want to get the distinct names, the IF function is used which returns 1 when a name appears for a region the first time and returns 0 when it appears again.
This ensures that the COUNTIFS function counts the first instance of a name as 1, the second instance of the name as 2, and so on. This means that it keeps expanding as you go down the column.įor example, in cell E2, the criteria ranges are $C$2:C2 and $B$2:B2 and in cell E3 these ranges expand to $C$2:C3 and $B$2:B3. Also, note that the criteria range is $C$2:C2 and $B$2:B2. The above formula uses the COUNTIFS function to count the number of times a name appears in the given region. Suppose I have the data set as shown below:Īdd the following formula in Column F and apply it for all the cells that have data in the adjacent columns. Let me first show you how to add a helper column and get a distinct count. While this is an easy workaround, there are some drawbacks to this method (covered later in this tutorial). Once you have added a helper column, you can then use this new data set to calculate the 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. Note: If you’re 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). Distinct names are listed in column B in the above data set. This can be achieved by removing the duplicate values/names and keeping all the distinct ones. So if a name appears three times, it’s still counted as one distinct name. Unique names are listed in column C in the above datasetĭistinct values/names are those that occur at least once in the dataset.
This means that all the names that repeat and have duplicates are not unique. Unique values/names are those that only occur once. While these may seem like the same thing, it’s not.īelow is an example where there is a dataset of names and I have listed unique and distinct names separately. Count Unique Values (not distinct values) in a Pivot Tableīut before I jump into how to count distinct values, it’s important to understand the difference between ‘distinct count’ and ‘unique count’ Distinct Count Vs Unique Count.
Add Data to Data Model and Summarize Using Distinct Count.Count Distinct Values in Excel Pivot Table.
0 kommentar(er)
