Pivot Table Sum Positive And Negative Numbers Regardless Of Sign in Row Label Range

Question

I am using Excel 2010, Pivot Table feature.

I would like to sum up the range of agent total sales. The problem is some of the sales person has the credit note. It means it shows negative in the figure. I would like the sum positive and negative number regardless their sign.

I manage to do it by manual enter {=SUM(ABS(B96:B102))} As the image show below.

However, My Pivot table is filter by monthly and product. Different filtering will have different row records. If I hard code the formula, it is not flexible as well. How can I make it flexible? I did try with GetPivotData, but it does not support by range.

Anyone can help?

Answer 1

Well, it depends. The formula you've entered in the question can be achieved by using a Calculated Field. Just define a new Calculated Field for your pivot table with the formula =ABS(Sales Amount) and display that instead of Sum of Sales Amount.

However, if your raw data contains both positive and negative values for a specific sales agent you need to take a different approach. The above Calculated Field operates on the already summed raw data.

If we for example have the following raw data:

Name Value
A     3
A    -2
B     4
B    -5

Then we create a pivot table and add a Calculated Field AbsValue with the formula =ABS(Value) we get the following result

  Sum of Value Sum of AbsValue
A  1           1
B -1           1

To get a summation of all the absolute values from the raw data, you need to add a new column to the raw data and perform the ABS function there:

Name Value AbsValue
A     3    3
A    -2    2
B     4    4
B    -5    5

  Sum of Value Sum of AbsValue
A  1           5
B -1           9

Answer 2

Under the field settings for the row label you're using, go to the second tab and click the option that's something like show field for no data. Then, the row labels will stay static and if there is no data associated with it it will just be blank, but your formulas will work.