How Change Default from Count to sum Pivot Table

[shonaart]

Hi
Please can you tell me how I can change default from count to Sum in a pivot table.
I know that if cells have no data in them it goes to count , but my spreadsheet is too large to go in and add zeros etc.

Thanks
Vaughan

 

[Yard]

I think you've answered your own question...it will default to SUM if all the values in the field are numeric. If just one is non-numeric, e.g. blank "", then it will assume a COUNT is the more appropriate function.

 

[shonaart]

Hi,
I know, so is it possible to force it to sum. Also it does not bring the correct number formate through. The spreadshet is in [H]:mm formate but bring another formate through. Very fustrating when you have a large number of fields.
Thanks
Vaughan

 

[Yard]

I don't believe so. If you forced it to SUM every time, what would you want to happen when it encountered a text value?

The number format issue can be frustrating. I use a bit of code to fix the number formats. Pretty simple, but it could be adapted. It assumes the active cell is within the Pivot Table you want to change.

Sub ConvertDataFieldsNumberFormat()
Dim strField As String, strNumFormat As String, strNumFormatDef As String
 
Set PT = Nothing
strNumFormatDef = "#,##0"
 
On Error Resume Next
Set PT = ActiveCell.PivotCell.PivotTable
On Error GoTo 0
If PT Is Nothing Then
    MsgBox "Not in a Pivot Table"
    Exit Sub
End If
 
strField = Application.InputBox("Enter the search term for the Pivot Fields to be changed:" & _
    vbCr & vbCr & "(leave blank to change all Pivot Fields)", strTitle, Type:=2)
If strField = "False" Then Exit Sub
 
strNumFormat = Application.InputBox("Enter the number format to apply to the Pivot Fields:" & _
    vbCr & vbCr & "leaving this blank will default to ""#,##0"", e.g. 1,000", strTitle, strNumFormatDef, Type:=2)
If strNumFormat = "False" Then Exit Sub
If strNumFormat = "" Then strNumFormat = strNumFormatDef
 
'you could turn off Screenupdating, Pivot table updating here if it runs slowly
For Each PF In PT.DataFields
    If InStr(1, PF.Name, strField) <> 0 Then
        PF.NumberFormat = strNumFormat
    End If
Next PF
'and turn it back on again here

End Sub