Pivot Table Sum

  • Thread starter Thread starter RedFive
  • Start date Start date
R

RedFive

I have created a PivotTable using data in another spreadsheet. For some
reason when I try to sum the data in the Data Field, all the values turn to
zeros. But if I leave it as a count the information is correct. Is it
because the data in the original cells are formulas? Any help or
suggestions would be greatly appreciated. Thanks
 
One way to test your theory is to take the data source & change them to
values instead of formulas. I've seen this occur when trying to aggregate
blank data.
 
Your formulas might be converting numbers to text. For example, if you
use LEFT or TEXT functions in the formula, the result will be a text
string, that might look like a number.
If that's what's happening, try typing two minus signs at the start of
the formula, e.g.:

=--RIGHT(A2,5)
 
Hi,

It's a little supprising that "all" the sums return 0. It suggests that the
entire range is text not numbers. If all the data are formulas in the data
area, then they all must be evaluating to text. You should be showing us
what the formula in the data area are. If all of the formulas in the data
area should be numbers but are text here is one way to clear the problem in
one fell swoop

1. Enter 1 in a blank cell
2. Copy that cell
3. Select the entire range of formulas and choose Edit, Paste Special,
Multiply.

All of your formulas will be retained but their results will all be numbers.

If this helps, please click the Yes button

Cheers,
Shane Devenshire
 
Back
Top