PivotTable Count Does Not Count Blank Cells

Exención de responsabilidades de contenido KB retirado

Este artículo se refiere a productos para los que Microsoft ya no ofrece soporte técnico. Por tanto, el presente artículo se ofrece "tal cual" y no será actualizado.

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
Propiedades

Id. de artículo: 149169 - Última revisión: 17 ago. 2005 - Revisión: 1

Comentarios