How to sum the result of calculated field on Excel's Pivot Table

Question

I'm working on a pivot table with some data that I want to round up. My pivot table currently has 1 text field for the rows and 2 numeric fields for the values. The field "Field 1" has the original numeric values from the data source, which contain decimals. I want all those values to be rounded up to integers, so I created a calculated field for the pivot table with the following formula: =ROUNDUP([Field 1], 0). The calculated field is named "Field 2". The formula effectively rounds up all values. However, the sum is not doing what I expect.

I'm attaching a screenshot to show this better:

.

What I want to do is to round up all original values and then add up all those new values. In the example, the result should return 2 in the totals row (1+1). However, what Excel is doing is first adding up all the values, which give a result of 0.83, and then rounding that result to 1.

I would appreciate any suggestions to fix this, whether through some pivot table option that I am missing or using VBA code. It is not possible to just round up the values in the source table since they are used for other calculations that would mess up if the pivot table data is filtered.

I already tried modifying the field options for the calculated field, playing around to show the max, min, sum, and other options. I also tried to sum up all the DataBodyRange of the pivot table in a different cell to get the result but I could not manage to get a dynamic reference. I could just do something like =SUM("A1:A2") which is static and will not automatically sum new data.

Answer 1

I don't think you can change the way how to calculate Roundup on pivottable total row. But you can sum it with VBA.

Option Explicit

Function GetTotal()
    Dim fld As PivotField
    Const HEADERNAME = "Sum of Field2"
    Application.Volatile
    Set fld = ActiveSheet.PivotTables(1).DataFields(HEADERNAME)
    GetTotal = Application.WorksheetFunction.Sum(fld.DataRange)
End Function