How to Get a Count in Excel Pivot Table Calculated Field - ExcelDemy (2023)

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.

❷ 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.

❸ 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.

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.

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.

❷ 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

❸ 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.

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

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

• How to Lookup a Table and Return Values in Excel (3 Simple Ways)
• Edit a Pivot Table in Excel (5 Methods)
• How to Insert or Delete Rows and Columns from Excel Table
• Use Excel Table Reference (10 Examples)

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,

❸ 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.

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.

❷ 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

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

• [Fixed!] Pivot Table Field Name Already Exists (2 Quick Methods)
• Insert A Pivot Table in Excel (A Step-by-Step Guideline)
• How to Refresh All Pivot Tables in Excel (3 Ways)
• Does TABLE Function Exist in Excel?