Auto-change to sum instead of count in Pivot

kkfighter

New Member
Joined
Mar 10, 2011
Messages
10
Hi All,

When i pull information into Pivot value, it always appear as count instead of sum. I need to manual change them back to sum and remove decimal place.
<TABLE style="WIDTH: 307pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=409 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 67pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=89></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" height=20></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Values</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" height=20></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Sum of Jan</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Count of Feb</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Count of Mar</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Count of Apr</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Count of May</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" height=20>Total</TD><TD class=xl67 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>88,806,442 </TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>437</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>437</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>437</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>437</TD></TR></TBODY></TABLE>

Any idea how to change it to sum easily without manual change all of them.

I do not want to fill up the raw data with all zeros to make pivot recognise it with sum.

Thanks,
KK
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Welcome to the board!

Give this code a try:

Code:
Sub SumAllData()
'changes data fields to SUM
On Error GoTo errHandler

Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet
Set ws = ActiveSheet
Application.ScreenUpdating = False

If PivotCheck(ws) Then
  For Each pt In ActiveSheet.PivotTables
    pt.ManualUpdate = True
    For Each pf In pt.DataFields
      pf.Function = xlSum
    Next pf
    pt.ManualUpdate = False
  Next pt
Else
  MsgBox "There are no pivot tables on the active sheet"
End If

exitHandler:
  Set pf = Nothing
  Set pt = Nothing
  Set ws = Nothing
  Application.ScreenUpdating = True
  Exit Sub

errHandler:
  GoTo exitHandler
End Sub

Hope that helps.
 
Upvote 0
Hi, I am also facing similar issue and I tried using ur VB code in the editor and I ran it but this code doesnt work for me.
Error message is - Compile Error: Sub or Function not defined.

Could you please help.

Thanks,
 
Upvote 0

Forum statistics

Threads
1,224,531
Messages
6,179,379
Members
452,907
Latest member
Roland Deschain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top