Pivot Table Shows Only Zeros

[DOUG]

My pivot table displays zeros instead of numbers. I have tried re-formatting
the numbers to "General" and "Number" and "Text". I have tried changing
"Count" to "Sum". I can see the correct numbers in the spreadsheet but not
in the pivot table. Please advise.

DOUG ECKERT

 

[Dave Peterson]

Changing the format of a cell doesn't change the value.

If your data is in A2:A99, then try this in two empty cells:

=counta(a2:a99)
and
=count(a2:a99)

=counta() will count the number of cells that have something in them.
=count() will count the number of cells that have a number in them.

If those formulas don't evaluate to the same thing (especially if =count()
returns a 0), then you have more to do to convert the values to real numbers.

Find an empty cell
edit|copy
Select the range to fix
Format as General
edit|paste special|values

This should convert those text numbers to number numbers.

Now back to your pivottable and refresh it.

Do you see the non-zero sum?

 

[DOUG]

Dave Peterson: Both COUNTA and COUNT returned the number one. Pasting the
value and/or format of the blank, general cell did not change the numbers in
the base table or in the pivot table. I did revert to the old trick of
multiplying the numbers in the base table by 1, but that did not work either.
I am open to ideas. I do have a sample of a very similar pivot table I
completed a couple of months ago that is correct, but all of the underlying
formatting appears to be the same. (How frustrating)!

DOUG

 

[Dave Peterson]

If both =counta() and =count() returned 1, then you only have something in a
single cell and it's a number.

Are you sure you pointed at the correct range?