How do I count unique values in a pivot table Excel 2010?

To get the distinct count in the Pivot Table, follow the below steps:

1. Right-click on any cell in the ‘Count of Sales Rep’ column.
2. Click on Value Field Settings.
3. In the Value Field Settings dialog box, select ‘Distinct Count’ as the type of calculation (you may have to scroll down the list to find it).
4. Click OK.

Why can’t I see distinct count in Excel pivot?

This is because you didn’t add the source data of the pivot table to the data model. Checking the box “Add this data to the data model” enables you to turn on distinct count feature in Excel pivot table.

How do you do a distinct count in Excel?

Right-click on any value in column B. Go to Value Field Settings. In the Summarize Values By tab, go to Summarize Value field by and set it to Distinct Count.

How do I count unique entries in Excel?

You can use the combination of the SUM and COUNTIF functions to count unique values in Excel. The syntax for this combined formula is = SUM(IF(1/COUNTIF(data, data)=1,1,0)). Here the COUNTIF formula counts the number of times each value in the range appears.

Is there a distinct count in Excel pivot table?

Excel Distinct Count or Unique Count. Pivot tables will offer a distinct count, if you check one tiny box as you create the pivot table. Here is an annoyance with pivot tables. Drag the Customer column from the Data table to the VALUES area. The field says Count of Customer, but it is really a count of how many invoices belong to each sector.

How to calculate distinct count of sales rep in Excel?

The above Pivot Table gives the total count of the Sales rep in each region (and not the distinct count). Right-click on any cell in the ‘Count of Sales Rep’ column. In the Value Field Settings dialog box, select ‘Distinct Count’ as the type of calculation (you may have to scroll down the list to find it). Click OK.

When did distinct count come out in Excel?

In Microsoft® Excel® 2013 and 2016, a new feature called “Distinct Count” was added which will return an accurate count of unique customers.

How to get distinct count in value field?

To get distinct count in the value field option you need to add a data model. this is great feature highlight to me and helped me 99%. i am stuck with one problem as the distinct count is also counting “0”. how to exclude “0” in distinct count using the pivot? This was exactly what I needed!