Go Back

How to Count Values in a Pivot Table

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.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

Leave a Comment

avatar