Odd Pivot Table Behavior

[btadams]

Hello Everybody!

I have a pivot table that is misbehaving. I have a huge table with lots of columns of numeric data that I made a pivot table from. Usually when I click on the checkbox next to a pivot table field to add it to the Values area of the pivot table it shows up as Sum of Column X. However some are showing up with just the column name and when I right-mouse click on them in the pivot table I get Field Settings... rather than Value Field Settings... for those that show up as Sum of Column X. Why?

I found that when I drag & drop those odd fields into the Values area they show up as Count of Column Y in the pivot table and when I right-mouse click on them in the pivot table it now has the Value Field Settings... option and I can change Count to Sum, which I couldn't do when I just checked the checkbox in the PivotTable Fields area.

Anyone know why this behavior is happening?

Thanks,

Brian

 

[ClaireS]

You get Count Of rather than Sum Of if there are ANY blank cells or text values in the column. Excel the treats the whole column as a text field, so you are seeing Field Settings rather than Value Feld Settings, because Excel doesn't think it's a Value field. Find and fix the non-numerical entries. Or live with having to change the Count Of to Sum Of manually!

 

[btadams]

Thanks Claire! It was about empty cells. Odd that I haven't run into this before.

 

[ClaireS]

You obviously have very well structured data as a rule .
Glad I was able to help.