Pivot Table Ignore Blanks

  • Thread starter Thread starter Ripan
  • Start date Start date
R

Ripan

Hello,

I think flavors of this question have been posted, but I wanted to ge
confirmation on the particular situation. I have a pivottable with
category row that I want to aggragate to (in this case, a location)
The numerical data is either a value or blank. Is there a way to set u
the data so that when the pivot table is counting the number of field
in a particular location, it only counts nonblanks? This would b
important not only for counting, but also for taking the average ove
only the non-blank fields.

Any help is appreciated,

Ripa
 
If those cells are really blank, then I think you'll get what you want.

My guess is that you have formulas in that field and the formula sometimes
evaluates to "". These look like empty cells, but they're not--they contain a
formula. So the pivottable counts them.

Maybe you could rewrite your formula to return 0's or 1's and use Sum to get the
count.

I'm not sure what you mean with the average question. =Average() ignores text
cells.
 
For MS Office 2010 users:
  1. Right Click in the Pivot Table
  2. Choose "Value Field Settings"
  3. Choose "Count Numbers"
 
Back
Top