2007 Pivot Tables

[Tom Hewitt]

Can someone advise if it is possible change something in Excel 2007.

Whenever I pivot any data the values fields ALWAYS comes up as count instead
of SUM. 99 times out of a 100 I need it to be SUM. I'm currently having to
go into each set of data and change to SUM each time. Anyone know if there
is a way to get it to automatically come up as SUM?

Or is the issue with the data not the pivot functions, however the data does
SUM when manually changed.

Many thanks in advance for any help provided

Tom

 

[Roger Govier]

Hi Tom

The default behaviour of the PT is
If All values in the field are Numeric, then use Sum
If Any values in the field are Text, then use Count

Although you are forcing the result to be Sum, there "could" be some values
which are text representations of numeric values.
These will be ignored in the Summation, hence the results you are seeing may
well not be correct.

You can check your source data by using a spare column and entering
=ISNUMBER(D2) change reference to the column of numeric data you are
testing.
Copy down and you will see FALSE against any entries that are incorrect.

 

[Tom Hewitt]

I'm guessing there is no way to force it to SUM.

I've checked the Data and the only fields that show as Text are ones that
are blank. All the fields that are meant to be number are number.

I could always add a new formula columnt to make even the blanks show as a
number, but that'd take just as long as changing the options to SUM.

Thanks for your help