ExcelInsider Team
Pivot tables are generally used for calculations. In a spreadsheet, Microsoft Excel usually shows the sum of the numbers in a pivot table. However, the sum is not the desired output all the time. One might want to count the number of instances in a pivot table as well. In this article, we will learn to count the number of instances in a pivot table.
➤ Click on the value field you want to count the number of instances.
➤ Select Value Field Settings from the context menu.
➤ From the new window, select Count from the Summarize Values By tab.
➤ Press OK.
In this article, we will go step-by-step on how to count the number of instances in a pivot table. Consider reading the full article if you want to get a clear idea of how things work and why we use the method we use.
Counting Number of Instances in a Pivot Table
We have a customer feedback sheet on our hands here. We want to know how many times one customer has repeated orders based on the satisfaction score. The issue is that, pivot table keeps summing up the satisfaction score, while we want a count. Here is how we fix the issue:
Step 1: Create a Pivot Table
Before doing anything, we need to create a pivot table from the regular table. Here is how to do that:
➤ Select any cell of the table
➤ Go to the Insert tab and select PivotTable.
➤ From the new window, press OK to create the pivot table in a new sheet.
➤ The pivot table will look like this at this stage:
Step 2: Prepare the Pivot Table
The pivot table is always blank at first. We need to add the fields we want to add and customize the pivot table according to our needs. Follow the steps below:
➤ Check the boxes that say “Customer” and “Satisfaction Score” from the PivotTable Fields panel on the right.
➤ Notice that the satisfaction score field already went to the values section with the sum, while the customer field went to the rows section. The pivot table will look like the following:
Step 3: Change the Field to Count
The pivot table shows the satisfaction score as the sum of the scores. We do not want that because we only want to check the repeating customers, not what scores they gave. Here is how we make the pivot table count the numbers instead of summarizing.
➤ Click on “Sum of Satisfaction Score” from the Values area in the PivotTable Fields panel to open a context menu.
➤ Click on Value Field Settings to open a window.
➤ In the new window, the Summarize Values By tab should be selected automatically. From there, find the Summarize value field by section and select Count from the list.
➤ Press OK to confirm.
➤ Now, in the pivot table, we can see three instances of Emily Johnson. This indicates the repeated purchase behavior of that person.
Frequently Asked Questions
How do you count the number of instances in Excel?
You can use the COUNTIF function to do so. Write the formula like below:
=COUNTIF(A:A,A1)
Here, the function will search the whole A column (determined by the first parameter, A:A), for the value in the A1 cell. If the value is repeated, the count will increase.
How to use a pivot table in Excel?
First of all, convert your data range to a regular table by selecting the data and pressing Ctrl + T . Then, from the Table Design tab, press Summarize with PivotTable from the Tools group. Choose the location of the pivot table and press OK. At first, the pivot table will have no data. You can add the fields by checking the respective boxes from the PivotTable Fields panel on the right.
How to refresh a pivot table?
Right-click on any cell of the pivot table to open a context menu. From there, click on Refresh to refresh the pivot table. You can press Alt + F5 instead to refresh the pivot table as well.
How to get distinct count in PivotTable?
By default, the distinct count function is disabled in a pivot table. If you want the function, make sure to create a data model while making the pivot table. Afterwards, go to the Value Field Settings of the desired field in the Value area, and select Distinct Count.
How to automate a PivotTable in Excel?
Assuming that you want to refresh the pivot table automatically in Excel, you can do it from the options. Right-click on the pivot table (i.e., any cell of the pivot table) and select PivotTable Options. Go to the Data tab and check the box that says “Refresh data when opening the file” from the “PivotTable Data” section. Hit OK to save the settings.
Wrapping Up
In this article, we learned how to count the number of instances in a pivot table. If you have benefited from this article, bookmark this site and revisit it whenever you have an issue working on your Excel worksheet. Download the workbook we used and leave a comment with your suggestions regarding future tutorials.
