When you add a field to a pivot table’s Values area, Count is the default summary function, if the field contains nonnumeric or blank cells. (If the field’s data are all numbers, Sum is the default function.)
The Count function’s name is slightly confusing, because it’s like the COUNTA worksheet function, not the COUNT worksheet function.
The pivot table Count function counts:
- text
- numbers
- errors
Blank cells are not counted.
Watch What You Count
If you’re using the Count function in a pivot table, be careful which field you use for the count. For example, in the pivot table source data shown below, cell C7, in the Qty column, is blank.
if you want to count the order for Pens, and use the Qty field, the blank cell (C7) would not be counted. The number of orders for Pens would show as 1. Instead, you could add the Product field to the Values area, and the Pens orders would show a count of 2.
In the pivot tables shown below, the one on the left uses Qty in the Values field, and the pivot table on the right uses Product in the Values field.
__________