Pivot counting

  • Thread starter Thread starter aablank
  • Start date Start date
A

aablank

I have data in a spreadsheet set up as follows:

Year Category A Category B Category C

When I set up a pivot table for it to divide and tally the
categories by year, it's like this:

Column - Year
Data - Count of Category A
Count of Category B
Count of Category C

What I get is:
1999 2000 2001
Count of Category A 7 14 84
Count of Category B 7 14 84
Count of Category C 7 14 84

The numbers aren't even right when I spot check. For
instance they are definitely different between Category A
and B in 1999. Any ideas on what I'm doing wrong?

I should tell you that this only seems to be a problem with
this data. Other data samples I've put together work just
fine. Could spaces be an issue?
 
What kind of data are you counting? If your data is actually formulas that
sometimes evaluate to "", then this'll count as an entry.

I usually try to use formulas that evaluate to numbers (0 and 1). Then I can
use "Sum of" and be happy.

Another problem is that if your worksheet contained formulas that evaluated to
"" and you did copy|paste special|Values, then those cells that look empty are
not.

One way to convert these cells to really empty cells is to select the range,
change (nothing) to a unique string (I use $$$$$$).

Then change $$$$$$ back to nothing.

Don't forget to refresh data on your pivottable.
 
=if(x+y>0,1,0)
Then use sum (not count).
(maybe a helper column if you need to keep the actual sum (x+y).)
 
Back
Top