Home > Pivot > Calculation > Calculated Field Pivot Table Calculated Field CountA 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 OnlyA 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. |
Video: Use Count in Calculated FieldWatch 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. Video Timeline
Video TranscriptIf 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 FieldHere is the full transcript for the video, UseCount in Calculated Field. Introduction: Use Count in Calculated FieldIn 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 TableSo 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 FieldTo 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 FormatNow 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 ProblemAnd 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 DataTo 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 FieldNow 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. |
Create a Calculated FieldNext, we'll create a calculated field, and check if the date field is greater than 2.
|
Calculated Field - Incorrect ResultsIn 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 IncorrectThe 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. |
Count Unique ItemsIn 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:
|
Count Unique Items with PowerPivotIn 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 |
Calculated Field BasicsIn 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. |
Get the Sample File
|
More Tutorials |
Last updated: May 9, 2023 3:45 PM