How to Get a Count in Excel Pivot Table Calculated Field

The Calculated Field is a powerful feature that elevates the potential of Excel Pivot Table to another level. This feature is used to analyze the values of some other fields in Excel Pivot Table using formulas. By default, the Calculated Field works on the sum value of the other Pivot Table field. But using a simple trick, you can work with the count value instead of the sum value. In this article, you will learn to get a count in Excel Pivot Table Calculated Field.


Download the Practice Workbook

You can download the Excel file from the following link and practice along with it.


Creating a Pivot Table

We have a sample order list in Excel Table form. We will turn this table into a Pivot Table in the following section.

It is convenient to turn an Excel Table into a Pivot Table rather than a normal data list. Because Excel Table will offer you a dynamic chart range. That means when you update your source data, your Pivot Table will update simultaneously.

Anyways, let’s see first how can we turn an Excel Table into a Pivot Table.

❶ First select the Excel Table. Then go to the INSERT menu from the main ribbon. After that click on the PivotTable option.

Insert a Pivot Table: pivot table calculated field count

❷ After that, a dialog box named Create Pivot Table will appear. You can change the Table Name from there. As well, you will have options to create a Pivot Table in a New Worksheet or in an Existing Worksheet. After tweaking the settings, hit OK.

Create Pivot Table dialog box: pivot table calculated field count

❸ Now from the Pivot Table Fields, select the fields that you want to have in your Pivot Table. For instance, I dragged the Customer field to the ROWS column and the Date field to the VALUES column.

Pivot table fields: pivot table calculated field count

After following the steps above, the Pivot Table will look like this:


An Issue with the Pivot Table Calculated Field

The main issue using the Calculated Field is that it works with the SUM value of the other fields in the Excel Pivot Table.

For example, we want to see the number of order dates against each of the customer names. So we have a column that shows the corresponding order dates against each of the customers.

Here, the number of order dates is a count value. And the serious number of the individual date is a bigger numerical value.

An Issue with the Pivot Table Calculated Field

So the problem with the Calculated Field is that it considers the serial number of the individual dates rather than the count value of the order dates.


Investigating the Problem

Let me show you the problem with the following steps:

❶ Create a new Calculated Field. To do that, click on a cell of your Pivot Table. Then go to the ANALYZE menu. After that from the Calculations group, select Fields, Items, & Sets. Under this option, you will find Calculated Field. Just click on it.

pivot table insert calculated field

❷ A new dialog box, Insert Calculated Field will appear. In the Name box, I’ve inserted >3. I want to see all the dates count greater than 3. That’s why it’s named so. Then, in the Formula box, type equal (=) first, then double click on Date from the Fields list. Then insert >3 in the Formula box and hit the OK command. So the whole formula is

=Date>3

insert calculated field in pivot table

❸ Now you will see that the new field shows the date instead of count values. To change it, right-click on a cell and go to Value Field Settings.

❹ At the bottom of the Value Field Settings, you will find the Number Format button. Click on it.

Field Value Settings

❺ Then the Format Cells dialog box will appear. Select General and hit the OK button.

Format Cells in Excel

Then you will see a new field has been added to the Pivot Table named Sum of >3.

You will see all the values in the column are 1. But according to the formula set in the Calculated Field dialog box, the digit 1 should represent date counts greater than 3 and the digit 0 should represent the counts less than 3.

This is the main problem of the Calculated Field. It’s not considering the count value of the Count of the Date column. Instead, it is using the serial number of the individual dates. As we know, the serial number of the individual date is much larger than 3, the Sum of >3 columns showing all 1.

Read more: Calculated Field Sum Divided by Count in Pivot Table


Similar Readings


Get a Count in Excel Pivot Table Calculated Field

So, we know the problem of the Calculated Field. In the following section, we will try to get the solution to it. So without having any further discussion, let’s dive straight into it.


A. Add a Helper Column to the Source Data

As the Calculated Field can’t read the count value of the fields generated by the Pivot Table, we will be adding an extra column to the source data named Helper.

This extra column will copy the values of the count value of another Pivot Table field. Thus, using the value of the helper column, the Calculated Field can show the count value properly.

To do that,

❶ Add an extra column to the source data called Helper. It will automatically get updated, as the source data table is an Excel Table.

❷ After that type the following formula,

=1
 in the first cell of the column and press the ENTER button. The rest of the cells of the Helper column will automatically copy the formula.

Add a Helper Column to the Source Data

❸ Now this newly added column hasn’t been updated to the PivotTable Fields list. To update, right-click on a cell of the Pivot Table and click on Refresh.

❹ Now you can see the Helper field in the PivotTable Fields list. Mark the Helper field and drag it to the VALUES column.

Pivot table fields

So, you can see that the Helper field is updated with the name, Sum of Helper. And this field has copied all the data from the Count of Date column.


B. Create a Calculated Field to Get the Count

Now let’s create another Calculated Field that will actually show the date counts greater than 3. To do so,

❶ Click on a cell of the Pivot Table. Then go to the ANALYZE tab. From the Calculations group select Fields, Items, & Sets. Under this option, you will find Calculated Field, just click on it.

Create a Calculated Field to get the Count

❷ Then the Insert Calculated Field dialog box will appear. In the Name box, I’m again using >3 to get the count of the dates greater than 3.

❸ In the Formula box, insert equal (=) first. Then double click on Helper from the Fields list. Then typer >3 and hit the OK command. So the ultimate formula is

=Helper>3

Insert Calculated Field: pivot table calculated field count

Now you will see that the new column, Sum of >3, is representing all the count values of date with 1 for count values more than 3 and 0 for count values less than 3.


Things to Remember

📌 Convert your data into an Excel Table first, before converting it again into an Excel Pivot Table.


Conclusion

To sum up, we have discussed the procedure of getting a count in an Excel Pivot Table Calculated Field. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.


Further Readings

Tags:

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo