Pivot Table Calculated Field Count

A pivot table calculated field always uses the SUM of other fields, even if those values are displayed with another summary function, such as COUNT. This tutorial shows a workaround to fix that problem, so you can get a COUNT in the calculated field.

SUM Function Only

A pivot table calculated field always uses the SUM of other values, even if those values are displayed with another summary function, such as COUNT.

In this example, the pivot table show a count of the Dates entered, for each product. We need to create a calculated field that will:

• check the count of dates for each product
• see if that count is a number is greater than 2

The video below show the calculated field problem, and how to fix it. There are written steps below the video.

Video: Use Count in Calculated Field

Watch this video to see how to create a pivot table, add a new counter field to the source data, and create a calculated field using the counter field. There are written instructions below the video.

Note: To follow along with the video, download the sample file, and go to the sheet named CalcFieldCount.

The full video Transcript is at the end of this page.

Video Timeline

• 00:00 Introduction
• 00:38 Build Pivot Table
• 01:59 Create Calculated Field
• 02:50 Change Number Format
• 03:11 Calculated Field Problem
• 04:03 Add New Field to Source Data
• 05:28 Create New Calculated Field

Calculated Field Problem

A calculated field always uses the SUM of other values, even if those values are displayed with another function, such as COUNT.

In this example, you'll see an example of this problem, where:

• Date field uses COUNT function
• Dates are summed, instead of counted, in pivot table calculated field

In the detailed steps below, you'll see these steps, that show the calculated field problem:

1. Create a pivot table
2. Change Date field to show Count
3. Create Calculated Field
4. Date count is ignored, and SUM is used instead

Next, you'll see the steps for fixing the calculated field problem:

1. Add a new field in the source data
2. Use the new field in the pivot table calculated field

Count the Date Field

First, to see the problem with using a field displayed as COUNT, we'll add the order Date field, and use it to show a count of orders.

1. Create a pivot table from the Orders data, with Rep and Product in the Row area, and Units and Total in the Values area
2. Add the Date field to the Values area, where it should appear as Count of Date.

This column shows a count of orders for each product, for each sales rep.

Next, we'll create a calculated field, and check if the date field is greater than 2.

1. Select a cell in the pivot table, and on the Excel Ribbon, under the PivotTable Tools tab, click the Analyze tab
2. In the Calculations group, click Fields, Items, & Sets, and then click Calculated Field.
3. The Insert Calculated Field dialog box opens -- this is where you can enter calculated field formulas
4. Type CountA as the Name
5. In the Formula box, type =Date > 2
   NOTE: the spaces can be omitted, if you prefer
6. Click Add to save the calculated field, and click Close.

   

7. The CountA field appears in the Values area of the pivot table, and in the field list in the PivotTable Field List.
8. The field is formatted as a Date, so change it to General format (right-click one of the values, click Value Field Settings, click Number Format)

In the above screenshot, you'll notice that all of the rows show 1, meaning the formula result is TRUE, even if the count is not greater than 2.

This is because Excel is using the SUM of the Date field, instead of the COUNT. The serial number for a date is much higher than 2 -- for example December 27, 2014 is equal to 42000. So, the SUM of even one date will be higher than 2, unless the date is Jan. 1, 1900.

Create an Orders Field

To get the correct count of orders, and use it in a calculated field, we'll add a new field to the source data on the Orders sheet. (This helper column has already been added in the sample file)

1. On the Orders sheet, add a new heading in first blank column -- Orders
2. In the cell below the heading, type a formula: =1

Because the sales data is in a named Excel table, the formula will automatically fill down to all the rows. It will also be automatically entered when you add new rows.

The 1s will give us a value that can be summed in a Calculated Field, to give correct results.

Calculated Field With Orders Count Field

To get the correct count of orders, and use it in a calculated field, we'll add a new field to the source data on the Orders sheet. (This has already been added in the sample file)

Next, we'll create a calculated field, and check if the Orders field is greater than 2.

NOTE: The Orders field does not have to be added to the pivot table before creating the calculated field that refers to it.

1. Select a cell in the pivot table, and on the Excel Ribbon, under the PivotTable Tools tab, click the Analyze tab
2. In the Calculations group, click Fields, Items, & Sets, and then click Calculated Field.
3. Type CountB as the Name
4. In the Formula box, type =Orders > 2
   NOTE: the spaces can be omitted, if you prefer
5. Click Add to save the calculated field, and click Close.

   

6. The CountB field appears in the Values area of the pivot table layout, and in the field list in the PivotTable Field List.

You'll notice that only some of the rows show 1, meaning the formula result is TRUE. A zero appears if the count is not greater than 2, meaning that the formula result is FALSE.

Remove Incorrect Fields

To complete the pivot table, you can follow these steps:

1. Remove the Count of Date field, and the CountA calculated field.
2. Add the Orders field, as Sum of Orders.
3. The Sum of CountA column heading can be changed to something more informative, such as "> 2".
4. The Sum of Orders column heading can be changed to "Orders " (with a space at the end of the name)

The completed pivot table will show the correct number of orders, and the check for products where more than 2 orders were sold.

Count Unique Items

In a pivot table, you may want to know how many unique customers placed an order for an item, instead of how many orders were placed. A normal pivot table won't calculate a unique count, either with a calculated fieldor with a Summary.

However, you could use one of the following workarounds:

1. Add the source data to the Data Model, in Excel 2013 and later.
• That creates an OLAP-based pivot table, which has a Distinct Count summary function
2. Use PowerPivot to create the pivot table, and use its Distinct Count function to create a unique count.
• See the details below.
3. Add a column to the source data, then add that field to the pivot table. There are instructions below

Count Unique Items with PowerPivot

In a pivot table, you may want to know how many unique (distinct) customers placed an order for an item, instead of how many orders were placed.

This feature isn't available in a normal Excel pivot table (see the workaround in the next section). However, if you have the PowerPivot add-in installed, you can use it to show a unique count for a field, by using the Distinct Count function.

Download the sample file for this video: StoreSales2012_2013.zip

Add a Column to Calculate Unique Counts

If you can't create an OLAP-based pivot table, from the Data Model, or PowerPivot, This workaround could help you create a unique count. However, it is a less flexible solution, so use it as a last resort.

In this example, the goal is to count the unique occurences of a Customer/Item order:

1. First, add a column to your database, with the heading 'CustItem'
2. In the first data row of the new column, enter the following formula that refers to the customer and item columns. For example, with Customer in column A and Item in column C:

  =IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))>1,0,1)

3. Copy the formula down to all rows in the database.
4. Then, add the field to the data area of the Excel pivot table.

In this example, you can see that 7 unique customers placed an order for binders, and there were 13 orders for binders. 

Calculated Field Basics

In Excel, you can add your own formulas in a pivot table, by creating a Pivot Table Calculated Field.

These fields can have simple formulas, such as:

• =Total * 3%

Or, you can create more complex formulas, like the one shown below:

• =IF(Units>100,Total*3%,0)

This video shows how to create a simple pivot table calculated field, and there are more examples on the Pivot Table Calculated Field page.

Transcript: Use Count in Calculated Field

Here is the full transcript for the video above -- Use Count in Calculated Field.

Introduction: Use Count in Calculated Field

In an Excel pivot table, you can create calculated fields, and in those refer to other fields in the pivot table.

Here we have an order list and we're going to create a pivot table, and see how many orders for each product were placed by each sales rep.

We're going to need to count things, and see if the number of products for each rep was over 2.

So the first step will be to create a pivot table and then we'll build a calculated field.

Build Pivot Table

So to insert the pivot table, I'll just select any cell in this table.

And on the Insert tab, click Pivot Table.

It's picked up the named table that I have created and I'm going to put it on an existing sheet.

I'll click in here and go to the sheet that I've got set up. Click where I want the pivot table and click OK.

So now I have a blank pivot table and I'm going to put a few fields in here.

I'll put in the Rep and the Product.

Then I want to see how many units they sold, so I'll check that box, and the total, the sales amount.

And I'd like to be able to see a count of the number of orders.

To do that, I'm going to use one of my existing fields, so I've got the date field here and I know there's a date in every record, so I can use that to count.

So I'll drag that into the values area, and it shows as a count of dates. Now we can see that there were three orders for binders, for the rep Andrews.

Now I'd like to create a calculated field that shows a one or a zero.

A one means true and a zero means false.

Just check this count and see if it's greater than 2.

Create Calculated Field

To do that, I'm going to select a cell in the pivot table, and under Pivot Table Tools, I'll click Analyze, then Fields, Items & Sets, and Calculated Field.

I'm going to type a name for this field and I'll call this CountA.

I can enter a formula here and all I want is a simple formula that checks the date.

So I'll double click it.

Then I'll type > and the number 2.

So that's my very simple calculated field.

So it should put a 1 for any row where the count of orders here, using Count of Date is greater than two.

So I'll click OK

Change Number Format

Now because I was using a date field, it's put that in date format.

So I'll right click. Value Field Settings.

And for this I'll click Number Format.

and I'll just use General and click OK.

Calculated Field Problem

And now we have ones, but it's showing a 1 everywhere.

Even though there's a 1 here.

I've asked it to show TRUE or a 1, if it's greater than 2, but it's showing a 1 for everything.

The reason for that is that a calculated field always uses a SUM.

Even if I have a field in here that's shown as a date, it ignores that and uses the SUM, and every date in Excel is a serial number.

So right now the date might be 42,000 and something.

So it doesn't take much to add up to be greater than 2.

So that's why we're seeing a 1, because the sum of every date here, whether it's just one order or several,

that serial number is going to add up to something much bigger than a 2.

Add New Field to Source Data

To solve this problem, we're going to have to add a field to the orders table.

So go up to where the headings are. I'll move this over.

I'm going to put in a new column here and I'm going to call this Orders.

This is a named table, so it just automatically expanded the table to include the new column, and I want to put an a 1 in this column for every row.

I'm just going to be able to use that, then, to count things.

It will sum up all the 1s ,and give me a correct count.

So instead of typing a 1 and then having to fill that down, and always remember to type a 1 in each new record, because this is a table, I can use a formula.

So I'll just type =1 and press Enter.

It then fills it down, it puts that same formula in every row, and it will automatically add that to new rows.

So it's a very quick and efficient way to get a counter field.

So every row here is one order.

And I'll be able to then use a SUM of this field to get the correct count, and use it in a calculated field.

I'll go back to my calculated field and I'm going to take out this CountA, because it's incorrect,

I'll remove the check mark from that and now we can still see the count of date.

Create New Calculated Field

Now before I can use the new Orders field, I have to refresh the pivot table, so that it will show up in the list over here. Right now I can't see it.

So I'll right click and refresh and now there's our new Orders field.

So now I'll build the calculated field. I've clicked a cell in the pivot table.

Under Pivot Table tools, click Analyze, Fields, Items, & Sets, Calculated Field

I'll call this CountB.

And our formula is going to be =

And I'll find our new Orders field and double click that.

That puts it in our formula. Then >2. I'll click OK.

And now we have our CountB.

It's showing as a SUM.

I didn't have to put that Orders field into the pivot table, in order to use that in our calculated field.

As long as it's in the source data, it can be used.

So now we can see that there's a 0 here because this is not greater than two.

And this is not greater than two.

So we're getting the correct results.

I can now take this Count of Date out as well.

I'll scroll up and remove its check mark and I could put the orders field in.

So now we can see our test, which is our calculated field. I can change the heading of this so it's a little more clear.

I'll just change the heading to >2.

And for orders I can change the heading here just to make this a bit narrower.

I'll type the word Orders, and a space character, because I can't use Orders, which is the exact match for a field name.

So Orders and space.

And now I could make these columns a little bit narrower.

So now we have a pivot table with a calculated field that is using a SUM of a counter field so we get correct results.

It's not going to work, if we try to use another field, that is just displayed as a count.

Get the Sample File

1. Download the sample file with the examples from these tutorials. The file is zipped, and is in xlsx format. The file does not contain macros: Calculated Field Examples sample file