Contextures

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

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.

count of Date field

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.

    count of Date field

  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)

count of Date field

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.

count of Date field

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.

    count of Date field

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

count of Date field

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 of Date field

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.

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
Search Contextures Sites

 

 

pivot power premium

 

 

 

160x600

 

 

 

Last updated: September 20, 2016 4:50 PM