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 Negative Total
Grand Negative Total
Is it possible to add such functionality?
Image for reference:
View Complete Thread with Replies
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: 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).
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.
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
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?
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,
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.
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.
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.
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
cix = cix + 1
' Reset Current Page value
Sheets("Customer P&L Pivot1").PivotTables(1).PivotFields("Cust 1A_Name").CurrentPage = "(All)"
MsgBox "Prints sent to printer."
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.
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.
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
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
ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:= _
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?
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;
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
pt.ManualUpdate = False
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 ?
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
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
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.
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.
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.
Sum Negative Values IF
Using Excel 97, Is there a way to Sum the values in columns if the value in cell B is a negative number. I want the total placed in their respected columns after excel finds 8 Consecutive Blank rows.
B1:B70 (remember the number can change it can also be B1:B80)
B1:B70 is a positive value, but then B85 is negative so begin the calculation from that point.
Sum B85,F85,G85,H85,I85,J85,K85,L85,M85,Q85,R85. And place the value in their corresponding cell after 8 consecutive blank rows appear in row B.
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.