Pivot counting

[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?

 

[Dave Peterson]

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.

 

[Dave Peterson]

=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).)