Tracker
HOME    TRACKER    Excel

# Pivot Table - Subtotal Of Negative Values

## I have data in a pivot table that has some positive and negative values. At the end of the Pivot Table, it sums the values for each month and then a grand total. I would like to also have it show the summation of all negative values per month. So it would be:Jan Total Jan Negative Total Feb Total Feb Negative Total etc... Grand Total Grand Negative Total Is it possible to add such functionality? Image for reference:

Related Forum Messages:
Reference To Subtotal In Pivot Table
I have a pivot Table with region & product wise as shown in the enclosed file. While calculating % of each item in region it is referring to grand total or total of column. Is there any way how to get this % calculated in regard of subtotal region.

Pivot Table >>Field Settings >Subtotal = Automatic
I create a Pivot Table in Excel 2003, excel by default puts the field settings for each of the columns to Automatic, creating a Total for each aggreate column, which is very annoying.

I have to manually go in in each field and change field settings > None. for each column, since no copy and paste special function to make all the columns have same subtotal >none.

Count Of Unique Entries As The Subtotal Of A Pivot Table
I have attached an example of what i am trying to do. I can work out how to calculate unique entries by putting in a formula under a pivot table, but is it possible to select an option in the pivot table settting which will give this result?

Subtract Negative Numbers Within Pivot Table Data Field
Ive attached a spreadsheet showing what im trying to achieve. basically i have a scheduling objective, in which only a few slots (AA, BB, CC) are available. so the starting availability is fixed. for this example assume the starting values are 6,7, and 8 respectively. as 'bookings' are entered, im using the value of -1 against A, B, or C respectively, to indicate that AA, BB, or CC should be decreased by 1 unit of availability.

im trying to incorporate the subtraction within a pivot table. as you can see i am way off. the hypothetical formula logic that im trying to implement within the PivotTable would look something like this:

for A,B or C, sum all (-1) values for each instance of Date and Stage... this will give total bookings

add this total value to the starting values of AA, BB, CC respectively to get the new availability

Pivot Table: Adding A Percentage Field As '% Of Subtotal'
In the attached Excel file, there is a pivot table.

In the Data part of the table, there are two columns. The second is the exact same field than the first one but expressed as percentage (Field settings/Options/Show field as "% of" + "Total".

The problem is: I don't want to express this field as a % of Total ('Grand total') but rather as a % of Subtotal (e.g. Danemark Total).

Userform Listbox: Check Wether Range Have Negative Values Or Not If Yes Load All Negative Values In The Listbox1 By Clicking Checkbox
I have data in range J2:J365 , H368:H401 & J403:J827. i want to check wether this range have negative values or not if yes load all negative values in the listbox1 by clicking checkbox.

Manipulating Pivot Table Values
I Am trying to create a pivot table. I have attached a sample of what i want. I have same kind of data for different dates. I would like for one field to list out seperately under each date and one field to sum up for the entire week( Total paid hrs). How do i go about doing this... is there a work around?

Pivot Table Saves As Values Only
We are using a file containing a pivot table in the 2007 version of Excel. Every time the file is saved under a new name for a new month, the pivot table becomes a table of values only (in other words it is no longer a pivot table!) The pivot links to an Access query (the 2007 version of Access). What is causing this?

Count Unique Values In A Pivot Table
I have a spreadsheet with several records for each person's name.
I want to have pivot tables based on various columns, with the data field being a count of unique occurrences of a person's name.

When I set up a basic Pivot, it counts each occurence of the person's name.

Copy Pivot Table Values And Formatting Only
In Excel 2007, is it possible to copy & paste a pivot table, and have the result look like a pivot table, but not actually be a pivot table? I want to keep the values and the formatting (the colors and borders, etc) but I want it to not actually be linked to the data or have the ability to change with dropdowns, etc. I've tried the usual copy & paste special (values) thing, and the other otions in the paste special box, but it doesn't keep the formatting.

Count Unique Values In Pivot Table
I have a worksheet with a list of employees and the workgroup they belong to, along with other data like manager, start dates, etc. Recently a couple of the workgroups were duplicated (change in managers), so these employees are showing up on two rows even though the workgroup has the same name. The only differences in the two rows are the workgroup effective start and end dates. I need to be able to count, in a pivot table, the number of unique employee/workgroup combinations there are per workgroup. I can add columns to the sheet, but it's a dynamic set of data that will grow each time it's refreshed...

Count Of Unique Values In Pivot Table
I'm trying to create a pivot table that will count how many employees have completed a Learning Plan. This task becomes complex (for me) because each learning plan has multiple Courses, each Course has a status of "Completed" or "Incomplete". A Learning Plan would only be considered "Completed" if all the courses within that Learning Plan were completed. In doing some research, it looks like I'll need to create another column of data, that shows per employee, per Learning Plan, if the entire Learning Plan has been completed, but I'm not sure of the best way to go about this. Please find SampleData attached.

Pivot Table Drilldown For Multiple Values
Is there a way to drill down the data of 100 values in a pivot table into one worksheet without resorting to drilling down each value, having 100 worksheets for each value, to paste into one worksheet? I got my company to give me an experimental computer to test for this in 2007, though if there is a way to do this in 2003,

Display Time Values In Pivot Table
why when pivot tables display individual or single time values they are rounded up or down. i.e. a value of 1:31.5 is displayed in the pivot table as 1:35.0 etc. I have tried custom formatting the table itself but to no avail.

Listing Unique Values And Summing W/o A Pivot Table
I have a set of data that I'm trying to identify the unique values in a column and then sum the related quantites against each of those values:

List Values From Excel Pivot Table Field
I have a Pivot Table with pivot fields and data. I would like, through VBA, to get the list of values that can be chosen from a given pivot field.

For example, a list would be Product1 / Product2 / Product3 .... I would like to read that list and put it into a drop down list in a form.

GOAL: I have several Pivot tables on the same sheet with similar fields and I want through macro to allow the user to update them all with one click.

Why Does Pivot Table Double My Values When It Is Set To Sum
I can't figure it out it simply doubles the value the only thing i can think of is the totals in question that are related to subtotaled columns but is not subtotaled

Sum The Highest 3 Values In A Pivot Table Row
I have a Pivot Table with 10 columns of data (numbers with a value of 0 to 1000) and a Total per row - all okay (1500 or so records).

For each row, I need to add the highest 3 values from the 10 columns to make a new total. Is there an easy formula I can add to the Pivot to do this?

Format Cells Based On Values In Pivot Table
I have a pivot table and next to it I added some calculations which are based on data from the table. In my "real" file, the cells next to the PT are formatted with conditional formatting based on other values in the sheet (see attached sample file). What I now need to do is add borders to the cells in columns “D:G” only if the cells in “B5” and downwards are not empty. I have tried doing it with conditional formatting but there are some other rules which contradict this setting.

The idea is to make the cells in columns “D:G” look like they are a natural extension on the PT. However, the table can grow or shrink (in length) and I want to see borders only around cells that are on the same rows as the data in column “B” (staring with B5). I think that whatever solution you can provide (if you can...) should be based a workbook_change event, because in my “real” file, the length of the table is changed whenever I select a different “page” of the PT.

Create Pivot Table: Cannot Open Pivot Table Source File
I'm trying to write a macro that will create a pivot table, and am getting an Error code 1004: Cannot Open Pivot Table Source File "Sheetname". My code is below. I've tried to note what each section does, and it all seems to work well except for the Pivot Table creation.

Pivot Table Query: Make A Pivot Table To Summarise The Data
attached is a spreadsheet 6 people in my area use daily(ive copied and pasted the sheet in question to a new worksheet, as the file was too big). Ive been trying for about 3 days now to make a pivot table to summarise this data.

List Values In Pivot Table Page Field Dropdown
I want to print one sheet for each of the values in a page field drop-down. I can do this by supplying the values individually (code below) but would rather determine the list of values dynamically. Can this be done?

Private Sub pbPrintAll_Click()
Dim cix As Integer
Dim Ctrct As String
cix = 3
While (Sheets("Database"). Range("B" + Trim(Str(cix))).Value <> "")
' Get value from database sheet
Ctrct = Sheets("Database").Range("B" + Trim(Str(cix))).Value
' Set CurrentPage value
Sheets("Customer P&L Pivot1"). PivotTables(1).PivotFields("Cust 1A_Name").CurrentPage = Ctrct
' Print formatted sheet
Sheets("Customer P&L").PrintOut
cix = cix + 1
Wend
' Reset Current Page value
Sheets("Customer P&L Pivot1").PivotTables(1).PivotFields("Cust 1A_Name").CurrentPage = "(All)"
MsgBox "Prints sent to printer."
End Sub

Pivot Table Labels Based On Comma Delimited Cell Values
"Is there a way to make values in a cell that are comma delimited (ex. cat, dog, fish) became separate labels in a pivot table instead of getting labels like 'cat, dog', 'fish, cat', 'cat, dog, fish' which represents the exact value in the cells.

The only real option I can think of would be to make an extra rows with the same data for each pet type. For example if the pets were 'cat, dog', there would be two almost identical rows except one would have 'cat' and the other 'dog'."

I have made a vba setup which can solve the problem. But it is pretty complicated and unstable. I need a solution which is not vba based. ~Or a least a very simple code!

Change The Date On One Of The Pivot Table And Pivot Table Match
I have data that develops 3 to 4 pivot table each day. I would like to know if there is a way to change the date on one of the pivot table and have the other pivot tables date change to match with the first pivot table. At this time I am going to all 3 or 4 pivot table to select the correct date. The date is in the page position of the pivot table. I have attached a small sample of the data and the pivot tables.

Average Of Pivot Subtotal
I have a pivot table with the following row data in this order: Customer - Order# - Part#. My data field is Sum of Total, ie the sum of the total \$ paid for the parts by order by customer. The pivot table provides me with a total by order, ie total \$ paid for parts by order, and sum of customer, ie total \$ paid for all parts for all orders. If I change the sum of customer to average of customer it provides me with average \$ per part but I want average \$ per order. I cannot find a way to average the subtotal by customer, ie what is the average order \$.

Pivot: Percentage Of Subtotal And Total
I have created an old fashioned pivot table. I would like to have it show percentage of subtotal and show percentage of Grand Total, to 3 decimal places. See REPORT tab. In column D of attachment, I have entered in text what I would like to see in this column for INCOME, EXPENSE, AND SPENDING pivot table.

Subtotal Of Data Table
My data sheet is attached for reference. The yellow cells show the end result I'm aiming for.

After each change in "NAME", I have inserted 2 blank rows. In the first blank row, column A, I want the text "ENDTRNS". In the second blank row:

column A - "TRNS"
column B - "Bill"
column C - reference the cell below
column D - "Accounts Payable"
column E - reference the cell below
column F - sum the next cell down to the last cell before the next blank row (sum range size will vary)
column G - reference the cell below
column H - reference the cell to the left
column I - "Y"

Then search out the next set of 2 blank rows and repeat, all the way down to the last set of blank rows.

Match Row That Contains Text Text String; List Associated Values From Pivot Table
refer to the attached workbook for reference. I am looking for a function in Sheet1, Column E that will search for the value of Sheet1, Column A within Sheet2, Column A. When a match is found, the function should look across Sheet2, Columns B - V for values of 1. When such values are found, the function should return the associated value from Sheet2, Row 2. There may be multiple values of 1, and as such, the function should separate values with a comma.

Copy Pivot Table & Paste As Values & Formats
my macro pulls download in on sheet1. On sheet2 it makes a pivottable of it with horizontally displayed the suppliers and vertically the codes of products. This is of course dynamic (one month it may contain 10 suppliers + 8 products, other month 15 suppliers + 20 products). On sheet3 is the (static) lay-out of all suppliers and all products. Now, what I want is that all fields <> empty (or zero) from the pivot table are to be copied and placed in sheet3, the 'report' I have to fill in. index and match won't do the job I think.

Months To Be Sorted In Ascending Order In Pivot Table, Want To Use Multiple Colors In Pivot Charts
My input data for Pivot table has a column named "Month". The month values are like April 07, April 08, Nov07 in random order for period between Jan 07 to Aug 08.

When I create a pivot Table, this column is sorted alphabetically (April 07 is followed by April 08) but I need it to be sorted in the ascending order with respect to month (April 07 is followed by May 07).

I further use this data to plot a Pivot Chart. There is another issue here. I want to use separate colors for each series. I do not know how to achieve above 2 things.

Convert Absolute Values To Negative Values Based On Criteria
is it possible to write a macro that will convert absolute values into negative based on criteria? Data gets downloaded from in house system which comes out as all absolute values, now i need the macro to covert absolute values to negative if the amount is either "Our Reciepts" or "Our Delivery"

Below is a small sample of data, real data is around 100 rows

Col DCol EOur Receipts1231Our Delivery1231Delivery Settlement1323

Refresh Pivot Tables Linked To Pivot Table
I currently have several pivot table that's linked to a single pivot table(let's call it X) in the same workbook. I'm doing this to limit the file size because the data in X comes from a text file that has millions of lines. However, it's such a pain every time I need to update the tables because simply clicking "refresh" does not update those tables that are linked to X with new data. I would have to instruct the wizard in every linked table to point to X every time. I'm trying to write a small program to re-point to X for each of those other pivot tables whenever i refresh data. However, after trying to record the steps to do this I'm still unable to run these

Sub Macro1()

ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:= _
"PivotTable1"

End Sub

Pivot Table Based Off Multiple Pivot Tables
Is it possible to create pivot table from another multiple pivot table.

Example: I have two diff pivot table "Income" and "Expense" as well
and I need to preapare new pivot table using with those two pivot table

Subtotal Rows & Summary Report Of Dynamic Table
writing a VBA to convert a set of data in sheet 1 to one in sheet 2(I am enclosing that as a Excel document"Test -Original").I have described what needs to be done ( step by step ) below.

Develop a macro
1. I have a report from SAP BW, the original format of which is in sheet 1.I need to develop a macro using VBA and need the report with the format in sheet 2.

2. I need a “Results “row after every Bill to Party in column A as below(screen shot 1.doc):

The number of customers is dynamic i.e.it keeps changing every month

4. Nothing needs to be done to column.SAP BW will not overwrite the format and the data in column G.Hence leave it as it is.

5. Calculate the number of Sales document numbers for each customer and put the value of 1 for every value. If it is blank it should not be counted and put the value of 0 for those rows. (Shown below) Display the sum of the number of sales document numbers in the results row for column H

6. In the column I, put the value of 1 if the difference column (column G) is 0 and put the value of 0 if the value in the difference column (column G) is any value apart from 0.now sum the value in the results row for each customer and display the summation value in the results row under column I

7. Compute the percentage which is the values in (column I/Column J)*100 .This should be done only for the results row

How To Mimic A Pivot Table Without A Pivot Table
I have a list of items and their associated quantities, many items appearing multiple times. I need a concise list that summarizes each item and sums all of its quantities.

The obvious solution is a pivot table. However, I update this list frequently and for some reason the pivot table is difficult to update. is there a function or simple vba code that I could put into this workbook that would work better than my unflexible pivot table?

Pivot Tables: Pivot Table Layout
if there is a way to display a table as column percentages but have the totals as raw numbers.

Count Pivot Fields In Pivot Table
I am trying to find a way to count the total number of pivot fields in a pivot table so I can remove ghost pivot items that are no longer in the pivot table data. My code for this subroutine is as follows;

Sub RemoveGhostPivotItems()
Dim ghost As PivotItem
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
pt.ManualUpdate = True
For Count = 1 To 10
On Error Resume Next
For Each ghost In pt.PivotFields(Count).PivotItems
ghost.Delete
Next ghost
Next Count
pt.ManualUpdate = False
End Sub

My code makes an assumption that I have 10 Pivot Fields or less. It would be nice to actually know the number of Pivot Fields so my "For Count" Loop would be more efficient. In otherwords;..............

Import Data From Access Table To Pivot Table - Enable Auto Refresh
I have enable Refresh on Open for my excel pivot table, but user need to click "Enable Automatic Refresh" , only solution i came across is to change the registry setting. Which i dont have access to edit registry(admin disable the access).

Alternate solution i try to use Access macro to automate the process and use Outputto save it as a excel file A. Then use excel file B to update pivot table from excel file A.(as excel A data is always latest)
The problem is i will get "....A file name already exist...do you want to overwrite.." prompt.
Which defeat the automate process.

Any other solution to enable the automatic refresh on open the excel workbook?

Or Access can overwrite the exist file or save it as another file name with timestamp ?

Find Largest Invoice For Each Individual Identifying Code Number In The Table Without Using A Pivot Table
Data Table including-

List of Identifying Code Numbers for customer invoices

Multiple repetitions of individual Identifying Code Numbers in list

Various data in table range including Various Values of invoices from different dates for each repetion of Identifying Code Number.

- Wish to find largest invoice for each Individual Identifying Code Number in the table without using a pivot table.

i have tried combining Max and Large functions with Vlookups etc.

Convert Positive Values To Negative Values
I have a table (column C5 to H20)

The table has values in each cell. Some cells have zero values and some have positive values.

Basically, I need to convert all positive (non zero) values to Negative values using VBA (i.e. convert 251 to -251).

Use Vlookup For Negative Values Or A Range Of Values?
i want to put a subject for a negative values, how should i do it?

For example:

lookup_value: -1000
table_array:
<0 - Poor
0-10000 - Good
> 10000 - excellent

is it possible to do this? ALternatively see the (new) attachment for clearer question.

Copying Subtotal Values
I have a large list of data including subtotals. I want to copy and paste just the values of the subtotals to another worksheet. I have tried collapsing the list to Level 2 and copying/pasting values but this still carries over the whole list.

Apply A Filter In A Pivot Table And Extract Results In A Table
I have made a pivot table and I dlike to identify with a macro the documents with net value over 1000. Then extract these values next to the respective sales documents in an are near the pivot table somewhere. The fields are called Document and Sum of Net value. Of course the pivot is very variable one time it has 3000 records and another 5000.

Pivot Table An Extract Of Each Data Contained In This Table
i have a pivot table an extract of each data contained in this table.

[img]Count of NAMdate
SERVICENAM12-oct10-déc11-décGrand Total
Commercial-lauralaura11
Commercial-laura Totalgh11

custody-jonathanjonathan112
k11
custody-jonathan Totalgh1113

settlement-ludovicludovic11
settlement-ludovic Totalgh11

SPQC-elodieelodie112
SPQC-elodie Totalgh112

Grand Total1337

Pivot Table: Table That Shows As A Header The URL
I want for my set of data. The attached .xls is pretty straight forward: the first column is a list of people (identified by their customer number) and the second column is the URL they visited.

Since many people went to multiple pages, there are dupes between the two columns, but all of the rows are unique. What i am looking for is a table that shows as a header the URL (just one) and then the list of people that went to that URL under the header. So it's really just one column of information. It seems like a perfect task for a pivot table.

Use The Worksheet Function Vlookup On Subtotal Values
Is it possible to use the worksheet function Vlookup on subtotal values? If so what is the formula? The problem that I am trying to solve is as follows. I have a sheet (1) listing part numbers that are not moving at a particular depot. On another sheet (2) there are details of sales relating to the non-moving part numbers at 6 other depots, I have added subtotals. Currently I am switching between worksheets to see if I can transfer stock. I would like to id on sheet 1 the total number of sales at other depots, then I can see what part numbers may be transferrable without a great deal of manual intervention.

SUBTOTAL IF Formula To Sum Distinct Values In A Column
I'm looking for a SUBTOTAl IF function (if there's any) that would sum all the distinct values in a column for a given criteria based on another column. My data is listed below:

Column A Column B
(Quarter) (Revenue)

Q1 2008 \$10
Q1 2008 \$10
Q1 2008 \$20
Q2 2008 \$15
Q3 2008 \$25
Q3 2008 \$25

I'm looking for a formula to sum the DISTINCT Revenue values for Q1 2008, Q2 2008, and Q3 2008 based on the autofiltered list. The formula result should change dynamically based on the autofilter selection.

Pivot, Want SOME Row Items Values On Pivot But All In Total
I have a pivot that shows Location, Dept and Total Sales by month. So Location and Dept are in the row area, Period is in the column area and Total Sales are summed in the page area.

Well, they decide they do not want to see Dept: 999 on the pivot. Thats easy, just click the dropdown and uncheck it....they are amazed at my ingenuity!

Well, they then notice Dept: 999 is no longer included in the Location total or grand total....makes sesne to me I say!

Well, they then specify that Dept: 999 should NOT appear on the pivot but SHOULD be included in subtotals / totals.

Subtotal With Condition (only Count Values Greater Than Zero When Data Is Filtered)
=SUBTOTAL(2,A2:A100) can be used in combination with any other formula so that when the data is filtered using autofilter it will only count values greater than zero.

I have attached a worksheet with the example.

In the attached workbook subtotal formulas are used in Row1 for sum and row 2 for count.
When the data is filtered using code "DUP" in column D, the subtotal in cell C2 is "4", whereas in the specified range there is a positive value in only 1 cell that is \$1050 in cell C99.

Is there some combination that can be used with SUBTOTAL formula so that it will only count values greater than zero when data is filtered.

Positive And Negative Values
way using vba to match a -negative value to a postive value in a same column and delete the entire row? (debits and credits)