Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to count distinctive values in Pivot Table. Let’s get started
In the caption below, we have a data set of 1040 transaction records. In addition, the data set is formatted in Excel Table using CTRL + T
We want to ascertain how many unique Customers purchased product from our Account Manager, how many customers repeated transaction; and the total value of the items purchased.
- Click anywhere inside the data set
- In the Tables group of the Insert tab, click on PivotTable or press ALT + NV on the keyboard
- In the Create PivotTable dialogue box, check the Add this data to the data model box
- Click OK.
Note, adding the data to the data model is required to enable Distinct Count in the Values Field Settings
- Click OK. A new PivotTable sheet tab is automatically created to build our report
- In the PivotTable Fields, drag and drop Account Manager into the Rows area
- Drag and drop Customer Name into the Values area twice
- Drag and drop Total into the Values area below Count of Customer Name2.
From the caption below, we notice quite a few things:
- When we dragged and dropped Customer Name which is a Text field into the Values area, the PivotTable displayed Count of Customer Name. This is how PivotTable operates whenever we drag any field that contain text and drop inside the Values area.
- Currency Format is applied to the Sum of Total
- Click on any value in Count of Customer Name
- In the Active Group of the PivotTable Analyze tab, click on Field Settings as seen in the caption above
- Or, right-click on any of the values in Count of Customer Name and select Value Field Settings
- In the Summarize Values By tab of the Values Field Settings dialogue box, scroll down and select Distinct Count
- Click OK
In the caption below, we noticed that there are 70 Customers that did transaction with Account Manager Abiola David. However, there are 51 unique customers out of the 70 overall customers. There are 19 customers that repeated transaction with Abiola David.
Furthermore, the value of the Total Sales for all the 70 Customers is ₦41,254.23
I hope you’ve learnt something new. Kindly do subscribe to my Youtube channel: Abiola David Excel Jet Consult