XL: PivotTable Count Does Not Count Blank Cells

ID: Q149169

5.x 7.00 WINDOWS kbusage

The information in this article applies to:

SYMPTOMS

When you create a PivotTable with a field that contains blank cells, if you specify the COUNT function for the "Summarize By" category, Microsoft Excel will find the blank cells in the column but the count for these blank cells will be 0.

Example:

1. Enter the data into the cells as follows: 

      A1:  1
      A2:  2
      A3:  3
      A4:  3
      A5:  5
      A6:  6
      A7:  7
      A8:  8
      A9:  <blank>
      A10: <blank>
      A11: <blank>
      A12: 9
2. Run the PivotTable Wizard and select field1 for row and data. Use Count 
   for the Summarize By category for field1.
You get the following results: 

   Count of field1
   field1                       Total
   ----------------------------------

   1                              1
   2                              1
   3                              2
   5                              1
   6                              1
   7                              1
   8                              1
   9                              1
   (blank)                     0

WORKAROUND

Use the COUNTIF function on the same sheet. COUNTIF will correctly calculate the number of blanks.

Example

To create this example, use the sample data from the "Example" section in the "Symptoms" section of this article.

In cell A17 type: 

   =""
In another cell, use the following formula: 

   =COUNTIF(A1:A12,A17)
This formula will evaluate to 3.

REFERENCES

For more information about the COUNTIF function in Microsoft Excel version 5.0, click the Search button in Help and type: 

   countif
For more information about the COUNTIF function in Microsoft Excel version 7.0, click Answer Wizard on the Help menu, and type: 

   countif
KBCategory: kbusage KBSubcategory:

Additional reference words: 5.00 7.00 COUNTIF() 

Version           : 5.x 7.00
Platform          : WINDOWS

Last Reviewed: March 29, 1997