Pivot Table Calculated Field Count

A calculated field always uses the SUM of other values, even if those values are displayed with another function, such as COUNT. This tutorial shows how to add a field to the source data, and use that when a count is required.

Calculated Field Basics

Add your own formulas in a pivot table, by creating calculated fields. These fields can have simple formulas, such as "=Total * 3%" or more complex formulas, like the one shown below, "=IF(Units>100,Total*3%,0).

Learn how to create a calculated fields, and other details on this page: Excel Pivot Table Calculated Field

Use a Count in a Calculated Field

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, we'll create a calculated field to check the number of orders placed for each product, to see if that number is greater than 2.

This example is shown on the sample file's CalcFieldCount sheet.

Count the Date Field

First, to see the problem with using a field displayed as COUNT, we'll add the 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. Type CountA as the Name
4. In the Formula box, type =Date > 2
   NOTE: the spaces can be omitted, if you prefer
5. Click Add to save the calculated field, and click Close.

   

6. The CountA field appears in the Values area of the pivot table, and in the field list in the PivotTable Field List.
7. 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)

You'll notice that all ot 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 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 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, 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.

Video: Use a Count in a 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.

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.
2. Use PowerPivot to create the pivot table, and use its functions to create a unique count. See the details below.
3. Add a column to the database, then add that field to the pivottable. Follow the 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 distinct count for a field.

Download the sample file for this video: StoreSales2012_2013.zip

Add a Column to Calculate Unique Counts

For example, to count the unique occurences of a Customer/Item order:

1. add a column to your database, with the heading 'CustItem'
2. In the first data row, enter a formula that refers to the customer and item columns. For example:

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

Download 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

Pivot Table Tools

To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:

• copy the formatting from one pivot table, and apply it to another pivot table.
• change all the values from Count to Sum
• remove the "Sum of" from all the headings

and much more!