How to Count Entries on a Pivot Table

by Paul Dohrman

Pivot tables are spreadsheet tools used to summarize a larger data table into a smaller one. In Excel, the default is to sum the data in each requested field combination. Suppose that you would rather count the frequency of data in a field combination instead of sum the numbers. Pivot tables allow you to do this for both discrete and grouped continuous data. You can count the number of times a certain value appears in the data, and even count frequency of occurrence within user-defined ranges.

Step 1

Define a table of purchase data--serving as a pivot table’s source--as follows. The underscores are used merely to maintain spacing and alignment. Day of Purchase__Price___ Sunday_________5 Sunday_________10 Sunday_________7 Sunday_________5 Monday________11 Monday________10 Tuesday________7 Tuesday________10 Tuesday________11 Tuesday________6 Wednesday______3 Wednesday______10 Wednesday______11 The default is for the pivot table to return a table summing the purchase prices: Day of Purchase__Price___ Sunday__________27 Monday__________21 Tuesday_________34 Wednesday_______24 But instead you’d like to change the pivot table to give you the frequency of purchases. Then go to the next step.

Step 2

Click on the "Pivot Table Wizard" button in the Pivot Table toolbar. Click the "Layout" button. Double-click on the "Price" field button. Select “Count.” Click “OK,” “OK” again, and then “Finish.” The pivot table will change to a frequency table of purchase counts. But suppose you want the frequency of purchase prices and don’t care anymore about day of purchase. Then go to the next step.

Step 3

Click on the "Pivot Table Wizard" button in the Pivot Table toolbar. Click the "Layout" button. Remove the “Day of Purchase” field from the Row area. Drag “Price” from the far right of the window over to the Row area. Now you have Price in both the left Row region serving as a row label, and in the Data region. Click “OK,” “OK” again, and then “Finish.” The resulting pivot table will look like this: Price___Price Count 3_________1 5_________2 6_________1 7_________2 10________4 11________3 Suppose that your data isn’t this discrete and you need to group the data some before the price counts will go above 1. Then go to the next step.

Group data by selecting a cell in the column to be grouped and right-click your mouse. A menu will pop up. Select “Group and Outline,” and then “Group.” Select the width you want each interval to have in the “By:” field. Select “OK.” The pivot table will now have collapsed into a smaller, more summarized table than before. If an interval has no data, the pivot table won’t show it.


About the Author

Paul Dohrman's academic background is in physics and economics. He has professional experience as an educator, mortgage consultant, and casualty actuary. His interests include development economics, technology-based charities, and angel investing.

More Articles