If there are blank cells in one of the fields in your source data, you might want to show a count of the blank cells in the pivot table. In this example there’s a Region field in the source data, and some of the records have no region name entered. In the pivot table, you’d like to see a count of how many records are missing a region name.

To find the count, you might add the Region field to the pivot table’s row area, and put another copy of the Region field in the data area, as Count of Region.This seems logical, but there’s no count showing for the blank regions.

Count a Different Field

A pivot table can’t count the blank cells when you add a field to the data area and use the Count or CountNum summary function. That’s why no count appears beside the (blank) region in the screen shot above.

Instead, put a different field in the data area, and use it for the count. For example, if the Units field will always contain data, add Count of Units to the data area, as shown below. The count of blank Regions is now calculated, and you can see that three records are missing a region name.