Microsoft KB Archive/149169

= PivotTable Count Does Not Count Blank Cells =

Article ID: 149169

Article Last Modified on 8/17/2005

-

APPLIES TO


 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0c

-



This article was previously published under Q149169





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:

 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:

A10:

A11:

A12: 9

 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.

