Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

enter image description here

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?

share|improve this question
up vote 3 down vote accepted

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
share|improve this answer
This is the alternative I did just now, I found out no way can direct access the pivot table range. – Shiro Apr 30 '12 at 10:04

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.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .