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

In the Formula box, type =Date > 2NOTE: the spaces can be omitted, if you prefer

Click Add to save the calculated field, and click Close.

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

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)

On the Orders sheet, add a new heading in first blank column --
Orders

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.

Select a cell in the pivot table, and on the Excel Ribbon, under
the PivotTable Tools tab, click the Analyze tab

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.

Use PowerPivot to create the pivot table, and use its functions
to create a unique count. See
the details below.

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.