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 shows a count of the Dates entered, for each product. We need to create a calculated field that will:
The video below show the calculated field problem, and how to fix it. There are written steps below the video.
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 transcript is below the video, as well as the written steps for this technique.
If you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript
Transcript: Use Count in Calculated Field
Here is the full transcript for the video, UseCount 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.
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:
In the detailed steps below, you'll see these steps, that show the calculated field problem:
Next, you'll see the steps for fixing the calculated field problem:
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.
This column shows a count of orders for each product, for each sales rep.
Create a Calculated Field
Next, we'll create a calculated field, and check if the date field is greater than 2.
Calculated Field - Incorrect Results
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.
For example, Andrews had only one date with a Pen order, with a total of 111.44
Why Result is Incorrect
The result looks incorrect, because Excel is using the SUM of the Date field, instead of the COUNT.
In Excel, each date is stored as a serial number.
So, for dates on or after Jan. 3, 1900, the SUM of even one date will be greater than 2.
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)
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.
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.
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.
To complete the pivot table, you can follow these steps:
The completed pivot table will show the correct number of orders, and the check for products where more than 2 orders were sold.
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 field or with a Summary.
However, you could use one of the following workarounds to get a count of distinct values:
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:
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:
Or, you can create more complex formulas, like the one shown below:
This video shows how to create a simple pivot table calculated field, and there are more examples on the Pivot Table Calculated Field page.
Last updated: May 9, 2023 3:45 PM