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

microsoft excelpivot table

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?

Best Answer

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

Related Solutions

Excel – Pivot table : absolute value of grand total in Excel

This issue is identified by Microsoft... see http://support.microsoft.com/kb/211470/en-us.

But I think a way to achieve this is to use an calculated item in the pivot. For example, if you have a dataset like this :

id | value
--
1  | 300 
--
2  | -400
--
3  | -500
--
4  | 600
--

A calculated item with this formula : =SUM(ABS(id[1])+ABS(id[2])+ABS(id[3])+ABS(id[4])) will produce a result of 1800, instead of 0 with the pivot table grand total. Notice that you need to have an "id" column in your data set. In the calculated field, id[i], with iindicate the item number as appears in the pivot table. For your case you have to iterate the formula until 20.

If you have a lot of rows or if the number of rows can vary, you could look to a macro for build automatically the calculated item.

Excel – Multiple Label Filters in a pivot table

To answer your question - no you cannot have more than one label filter or more than one value filter on a single pivot.

Raystafarian did not understand you question..

yes - clicking the "allow multiple filters" check-box will allow ONE label and ONE value filter BUT not two of the same type of filter.
yes - of course you can go ahead and select "multiple items" but that is not the label or value filtering you are looking for

Workaround 1

Filter on the table for the first label filter, then copy and paste the filtered table into a new table and then pivot on the new data set(table).. not a best option but will certainly get the job done

Workaround 2

IF you can work through an if or statement this will work great for your purpose and you can have as many label filters as your heart desires.

Basically you would create an additional column on your data set that will return whether or not the label contains the value/string you are looking for, then you can pivot using the new column.

First the basic formula with one criteria:

=IF(ISNUMBER(SEARCH("Apple",A1)),"Contains","Does not Contain")

a. The SEARCH() function searches a cell for the "apple" text and returns the character number where that text begins within the cell

b. The ISNUMBER() function will return a true or false value if the search is successful

eg1. if the search function is not successful an error will return(not a number) so the ISNUMBER() function will be false.
eg2. if search function is successful a number will be returned, so the ISNUMBER() function will be true

c. The IF() statement will return the personalized message "Contains" if true and "Does not Contain" if false

Now the formula with multiple criteria

=IF(OR(ISNUMBER(SEARCH("Apple",A2)),ISNUMBER(SEARCH("banana",A2))),"Contains","Does not Contain")

just throw an or statement before your first logic, which is ISNUMBER(SEARCH()) and so on for as many more criteria as you want