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. That can cause results that look wrong, but the video below shows a workaround to fix the problem.
Pivot Table Calculated Field Problem
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:
- check the count of dates for each product
- see if that count is a number is greater than 2
The “Sum of CountA” calculated field, in the screen shot below, says that 12 is greater than 2.
But, it also calculates that 1 is greater than 2, and 2 is greater than 2!
Calculated Field Workaround
Even though the Date field is showing as a count, Excel ignores that setting, for formulas in a calculated field.
Instead, the calculated field uses the SUM of those dates, not a COUNT.
Excel stores dates as serial numbers, starting with January 1, 1900, as number 1.
- Today is May 10, 2023, and that is serial number 45056.
- Any single date after January 2, 1990, would have a sum greater than 2!
- That’s why the result in the Sum of CountA column is 1 (TRUE) in every row
Fix the Calculated Field Problem
The video below shows the calculated field problem, and how to fix it.
Instead of counting an existing field, such as Date, you can add a new field, as a counter.
In the screen shot below, the CountB calculated field shows a zero (FALSE), in any row where the count is NOT greater than 2.
Video: Use Count in Calculated Field
Watch this video to see how to create a pivot table, and a calculated field, that doesn’t work the way we need it to.
To fix the problem, see how to add a new counter field to the source data, and then create a calculated field using that new
counter field.
Video Timeline
- 00:00 Introduction
- 00:38 Build Pivot Table
- 01:59 Create Calculated Field
- 02:50 Change Number Format
- 03:11 Calculated Field Problem
- 04:03 Add New Field to Source Data
- 05:28 Create New Calculated Field
Video: Create Pivot Table Calculated Field
In a pivot table, you can create your own calculated fields. In this video’s example, I show how to calculate each salesperson’s bonus, based on their total sales.
Download the Sample File
To see the source data, and the Calculated Fields, you can download the sample file from my Contextures website, on the Calculated Fields – Count page.
The zipped file is in xlsx format, and does not contain macros.
_______________
Pivot Table Calculated Field Counting Problem
______________