We can count values in a PivotTable by using the value field settings. This enables us to have a valid representation of what we have in our data. For instance, in the example below, there is a count of 16 for clients when distinctly, they are only 4. The steps below will walk through the process of counting values and unique values in a Pivot Table.
Figure 1- How to Count Values in a Pivot Table
Setting up the Data
- We will open a New excel sheet
- We will input the data as shown in figure 2 into the excel sheet
Figure 2- Setting up the Data
- We will click on anywhere on the data, click on the Insert tab, and click on Table as shown in figure 3
Figure 3- Putting the data in a Table
- We will click on OK on the dialog box that appears
Figure 4- Create Table Dialog Box
Figure 5- Created Table
- We will click on the box below Table Name under File in Figure 5 and name the Table as Client_Data
- We will press enter after inserting the name
- Now, we will create a Pivot Table with the Data
Creating the Pivot Table
- We will click on anywhere on the table
- We will click on the Insert tab and click on Pivot Table as shown in figure 3
Figure 6- Creating the Pivot Table
- We will click on existing worksheet and specify the Location where the Pivot table will start from (E3). We will also check “Add this Data to the Data Model”
- We will click on OK
- We will check the fields (Country, Client, and Expenditure) we want to add to the Pivot Table.
Figure 7- Selecting Pivot Table Fields
- We will drag each field to the respective position as shown in figure 7
Figure 8- Created Pivot Table
- We will notice that the Pivot table has taken a count of all client and didn’t consider the repetition of client names. An instance is in United States that has just Judy Creations, M and B, and Sam Furnitures as clients.
Counting Unique Values in a Pivot Table
- We will click on any count in Column G of the Pivot Table
- We will right-click and click on Value Field Settings
Figure 9- Value Field Settings Dialog box
- We will select distinct count in the “summarize values by” field.
- We will click on OK
Figure 10- Count of Unique Values in a Pivot Table
Instant Connection to an Expert through our Excelchat Service
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment