Totaling blank cells in Pivot Tables

[Guest]

I have a Pivot Table that shows blank cells but does not calculate the number
of cells. There are 3 records with blanks.
No 262
Yes 2
(blank)
Grand Total 264

In a Pivot Table I received from someone else, the blank fields are totaled.
Shanna Cleveland 10
Soraya Guzman 2
Steve Vitale 1
(blank) 7505
Grand Total 7709

I have checked the Table Options and both Pivot Tables have a check mark in
the "for empty cells, show:". I can not figure out what else might be
different between the two tables. Also, both workbooks have "Zero Values"
checked in the Tools/Option/View.

 

[Guest]

Hi

This may be a rather daft question, but are you sure there is any data in
the blank cells in your source data? It the blank rows contain data there
should be a count, sum, whatever in your table. If there is endeed data try
selecting the entire range and make a new table.

HTH.

/Sune

 

[Guest]

Yes, there is data in the 3 rows. My Pivot Table is counting a non-numeric
field and there are 3 records that are blank in that field. When I double
click on what should be the total (for the blank fields) it drills down with
the 3 rows. The Grand Total also does not include the number of blank
fields.
All of my Pivot Tables are this way, I have already tried to delete and
re-do the Pivot Table. I was thinking that there is something on my Excel
workbook set-up that might be different than the person that sent me a Pivot
Table with the blank fields totaled, but I wasn't able to find anything under
the Tools/Option. I am not sure where else to look.

 

[Debra Dalgleish]

The pivot table can't count blank cells, so if you put a field (e.g.
Reply) in the row area, and Count of Reply in the data area, the (blank)
item will show nothing in the data area.

However, if you add a different field to the data area, you may see the
correct count. For example, if the Date field always has a value, add
that to the data area, and summarize by Count. The pivot table will then
show the count of records with a blank Reply field.

 

[Guest]

Sorry, I'm drawing all blanks then. I can't even replicate the problem. Where
is the data comming from (manually typed in, generated by a query, formula
based)? What happens if you start with a blank workbook and manually enter
data that mirrors (some of) your current data and then make a PT based on
that?

 

[Guest]

If the pivot table can't count blank cells, can you explain why the 2nd
example I sent counted the blank cells? The 2nd example is a pivot table I
received from someone else. It apears to be set up the same as mine,
counting non-numeric fields (in a column) that contains blanks. These fields
are truly blank as indicated by the (blank); however, this pivot table
counted 7505 records.

I have deleted the pivot table and recreated it, but all my pivot tables do
the same thing. We both work at the same company, so the versions of excel
are the same.

I can't figure out what is different between the 2nd example and the one I
created.

 

[Guest]

Both pivot tables are based on data that is manually typed in, no formulas.
All my pivot tables are the same, they do not count blank cells, in this
workbook and all workbooks. This is why I thought there was something on the
Excel side and not within the pivot table. Both of us work for the same
company and are using the same version of Excel.

 

[Debra Dalgleish]

The difference is what field is in the data area.
What field is in your pivot table's data area, and what field is in the
other pivot table's data area?

 

[Guest]

The pivot table that does not count the blanks is based on two non-numeric
fields in the Row area and a Count of one of those fields in the Column area.
The pivot table that counts the blank fields is the same but only one
non-numeric field in the Row and a count of that field in the Column.