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: <blank>
A10: <blank>
A11: <blank>
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:
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:
For more information about the COUNTIF function in Microsoft Excel version
7.0, click Answer Wizard on the Help menu, and type:
Additional query words: 5.00 COUNTIF() XL
Keywords: KB149169