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.

## 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 with 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

`=Date>3`

❸ 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

## 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`

**ENTER**button. The rest of the cells of the

**Helper**column will automatically copy the 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

`=Helper>3`

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.

**Read More: **How to Apply Excel COUNTIF with Pivot Table Calculated Field

## Things to Remember

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

**Download the Practice Workbook**

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

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

## Related Articles

- Pivot Table Calculated Field for Average in Excel
- How to Insert a Calculated Item into Excel Pivot Table
- How to Calculate Variance Using Pivot Table in Excel
- How to Calculate Weighted Average in Excel Pivot Table

**<< Go Back to Calculated Field in Pivot Table | Pivot Table Calculations | Pivot Table in Excel | Learn Excel**