Create Calculated Field With a Count

In addition to using fields from the source data, you can create calculated fields in a pivot table, to add your own formulas. For example, add a field that multiplies the total sales by 3%, to show a Bonus amount.

You can learn the basics of Calculated Fields on my Contextures website.

pivotcalculatedfield00

Problems With Fields Shown as COUNT

Sometimes a Calculated Field doesn’t show the results that you expect. For example, if you show a field that uses the COUNT function, then try to use that count in your Calculated Field, you’ll run into problems.

The Date field is being counted in the screen shot below, and the calculated field – CountA – is checking for counts that are greater than 2.

As you can see, all the rows show a result of 1 (TRUE) in the CountA column, even if the result is not greater than 2.

calculatedfieldcount03

Fix the Problem

This problem occurs because a calculated field always uses the SUM of another field, even if that field is displayed as a COUNT.

To get correct results, you can add a new field to your source data, to act as a counter. Then, use the new field in the calculated field, and its SUM will be used, for the results that you expected.

To fix the problem in this pivot table, I added a new field – Orders – in the source data, to act as a counter. Each row will contain a 1, and those 1s can be summed, and used correctly in Calculated Fields.

calculatedfieldcount04

When the Calculated Field is changed, to use the Orders field, instead of the Date, the results are correct. Rows with 2 orders, or fewer, show a zero (FALSE) in the CountA column.

calculatedfieldcount06

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.

Video: Create Calculated Field With a Count

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.

Or watch on YouTube: Create Calculated Field With a Count

____________________

This entry was posted in Calculations. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *